In any version of DAX, you can aggregate data by grouping one or more columns using SUMMARIZE and/or ADDCOLUMNS. In Excel 2016, Power BI Desktop, and Analysis Services 2016, you have a new version of DAX that we identify as DAX 2015.
The new GROUPBY function in DAX 2015 provides a simple and powerful syntax to aggregate data. However, can you mimic the GROUPBY behavior with the previous version of DAX without using GROUPBY? This challenge shows you a problem that demonstrate why GROUPBY can be very useful for post-processing small result sets obtained by other table functions.
Scenario
You have a simple data model with Sales and Customers. You are performing an analysis based on the number of children of customers and whether the children are still at home or not.
The Challenge
You need to produce a simple report that divides customers in two categories based on whether all of their children are still at home or not, and finally show the number of customers and their sales based on continent and this new category.
The final report should look like this:
If you were to produce the report with the latest version of DAX, you could use the GROUPBY function with this query:
EVALUATE
GROUPBY (
ADDCOLUMNS (
SUMMARIZE (
Customer,
Customer[Children At Home],
Customer[Total Children],
Customer[Continent],
"Children Status", IF (
Customer[Children At Home] = Customer[Total Children],
"All at home",
"Children Left"
)
),
"Customers", CALCULATE ( COUNTROWS ( Customer ) ),
"Sales", [Sales Amount]
),
Customer[Continent],
[Children Status],
"Amount", SUMX ( CURRENTGROUP (), [Sales] ),
"Count", SUMX ( CURRENTGROUP (), [Customers] )
)
ORDER BY
[Continent],
[Children Status]
However, how do you write an equivalent DAX query if the report has to run on the previous version of DAX, where the GROUPBY function is not available?
You cannot create a calculated column and… sorry but no, suggesting your customer to upgrade to the new version is not a viable option. You have to solve the puzzle by writing DAX code with the functions available in the previous version of DAX (Excel 2013, Analysis Services 2012/4) and without modifying the data model.
Hints
You will probably try to use a simple SUMMARIZE but, as you will see, you cannot perform a grouping based on a calculated column. Grouping with SUMMARIZE or ADDCOLUMN only works with physical columns and this, by the way, shows you how powerful and useful the new GROUPBY function is.
Thus, the hint is that a simple SUMMARIZE is not the way to go.
Solution
To see the solution, just press the button below.