Overview
So far, we have only retrieved data from a 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.
Explanation
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 the customer’s 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])))
Haroon Ashraf’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.
He holds BSc (Gold Medal) and MSc Degrees in Computer Science and also received the OPF merit award.
His programming career began in 2006 working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Business Intelligence and Database Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration.
He has recently earned “Knowledge Management and Big Data in Business” certificate from The Polytechnic University of Hong Kong.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2020 | Author of the Year Contender – 2018-2020
