SQL Server DAX Summarizing Data

By: Siddharth Mehta

Overview

After the data is properly filtered, it's time to shape the data. One essential part of shaping the data is grouping the data, and summarizing a large dataset into a relatively smaller aggregated dataset. In DAX, one of the basic functions that facilitates this is the SUMMARIZE function. In this chapter we will look at the SUMMARIZE function.

Explanation

Open SSMS, connect to the tabular SSAS instance where the AdventureWorks tabular model is deployed, and open a new MDX query window. All the perspectives that we saw in the last chapter, would be listed in the Cube pane. Select the Internet Operation perspective in the Cube pane, and type the below expression.

evaluate ( 
 SUMMARIZE('Customer', [Gender], "Total Income", SUM ('Customer'[Yearly Income]) )
 )

Execute the above mentioned expression and you should be able to find the results as shown below. In this expression, we are trying to find the total of the yearly income field grouped by the gender of customers. In the summarize function, the first argument is the table name, the second argument is the field by which we want to group the dataset, an alias for the aggregated field, and then the expression. We have specified that once the dataset is grouped by the specified field, create a sum of Yearly Income with an alias of Total Income.

Let's say, that we need to group by more than one field and have multiple aggregated columns. We can add multiple fields for grouping and aggregation using the summarize function as shown below. In this example we are grouping the Customer table by Gender and Marital Status and we are aggregating the Total Income and Total Care fields.

evaluate ( 
 SUMMARIZE
  (
  'Customer', [Gender], [Marital Status], 
  "Total Income", SUM ('Customer'[Yearly Income]),
  "Total Cars", SUM ('Customer'[Total Cars Owned])
  )
  )

In this way, we can group data using the SUMMARIZE function. You can read more about the SUMMARIZE function here.

Additional Information

• Try out different DAX Statistical functions like the SUMMARIZE function to create complex grouping criteria.
• A new function named GROUP BY has been introduced with SQL Server 2016 which facilitates grouping in a similar manner. You can read more about this here.