DAX in SQL Server Summarize Statement to Group Data

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])))