By: Haroon Ashraf
Overview
So far we have only retrieved data from table or column, but what about grouping data and applying aggregation. The Summarize statement is used for instant grouping of data (columns) to apply aggregate function(s). In its simplest form, Summarize can be used to display tabular data without grouping as well.
Getting Product ID and Name
For example in order to see product name with IDs we can simply write the following DAX query using the Summarize statement:
EVALUATE(SUMMARIZE(Product,Product[ProductID],Product[Name]))
Getting All Customers Who Placed Orders
Summarize requires the table name followed by the columns to be grouped followed by the name of the aggregate column followed by the aggregation.
Columns from other tables can be directly used without the need of join as long as their relationships are already established. For example to see customers name and the dates they placed their orders, the following DAX query can be written:
EVALUATE(SUMMARIZE(Orders,Orders[OrderDate],Customer[Name]))
Getting All Customers with Orders Sorted By Most Recent First
Let's write a slightly complex DAX query to see all the customers who placed their orders sorted by most recent orders first simply adding Order By as follows:
EVALUATE(SUMMARIZE(Orders,Orders[OrderDate],Customer[Name])) ORDER BY Orders[OrderDate] DESC
Getting Number of Orders Placed by Each Customer
To see how many orders each customer has placed (group orders by customer) the following script is used:
EVALUATE(SUMMARIZE(Orders,Customer[Name],"Total Orders",COUNT(Orders[CustomerId])))
Getting Number of Orders by Type
If we want to know number of orders by order type use the following script:
EVALUATE(SUMMARIZE(Orders, OrderType[Name],"Total Orders",COUNT(Orders[OrderTypeId])))
Getting Orders per Month
To see all the orders per month we are leveraging time intelligence by accessing the Date column Calendar Month:
EVALUATE(SUMMARIZE(Orders,DimDate[MonthName],"Orders Per Month",COUNT(Orders[OrderId])))
