In this writing, I want to ask and learn from readers about your use cases of the SUMMARIZECOLUMNS DAX function in Power BI.
But before I ask and learn from you, I want to share what are my use cases of this DAX function.
When I first time learned about the SUMMARIZECOLUMNS DAX function, almost all books, blog posts, and Youtube videos were saying that it is one of the most compelling TABLE DAX functions, except one book.
Of course, I did not read all the existing books that talk about DAX, but on page 408 of that book (I believe you know what book I am referencing ➡️ "The Definitive Guide to DAX / Second edition"), it explains what and why there is a strong limitation in using the SUMMARIZECOLUMNS DAX function. It describes that under the environment where a context transition happens, the function will not be evaluated and it will show an error. It was quite difficult to understand this when I first time read this. At that time, even though I did not fully understand it, I tried not to use the SUMMARIZECOLUMNS DAX function in calculated measures to visualize data. I only used it when creating calculated tables and card visualizations. Those two cases are the cases I can control not to create a context transition environment. In fact, those are the only cases for me to use this until now, which makes me eager to learn more about the SUMMARIZECOLUMNS DAX function.
Examples of my cases are described below.
Calculated tables without NONVISUAL DAX function
By using the sample data model that looks like the below, I author the below formula using SUMMARIZE DAX function. The target is to create a calculated table that is a subset of a summarized version of the current data model.
Orange and Red sales and allsales = SUMMARIZECOLUMNS ( 'Calendar'[Year], 'Calendar'[Month-Year], Country[Country], 'Product'[Color], TREATAS ( { "Orange", "Red" }, 'Product'[Color] ), "Sales", SUMX ( Sales, Sales[Quantity] * RELATED ( 'Product'[Unit price] ) ), "Allsales", CALCULATE ( SUMX ( Sales, Sales[Quantity] * RELATED ( 'Product'[Unit price] ) ), ALLSELECTED ( 'Product'[Color] ) ) )
The critical point in this DAX formula is understanding the last column, [ALLsales]. The numbers in this column mean that those are the total of Orange + Red under the same category of the year, month-year, and country. To validate it, I filter the table in the Data view like below.
Calculated tables with NONVISUAL DAX function
By using the NONVISUAL DAX function like below, it shows a slightly different table.
Orange and Red sales and allsales NONVISUAL = SUMMARIZECOLUMNS ( 'Calendar'[Year], 'Calendar'[Month-Year], Country[Country], 'Product'[Color], NONVISUAL ( TREATAS ( { "Orange", "Red" }, 'Product'[Color] ) ), "Sales", SUMX ( Sales, Sales[Quantity] * RELATED ( 'Product'[Unit price] ) ), "Allsales", CALCULATE ( SUMX ( Sales, Sales[Quantity] * RELATED ( 'Product'[Unit price] ) ), ALLSELECTED ( 'Product'[Color] ) ) )
Together with the NONVISUAL DAX function, the last column [ALLsales] means that it is the total of Orange + Red + all other colors under the same category of the year, month-year, and country. To validate it, I filter the table in the Data view like below, and created a visualization to check what are the sales of all colors.
Card visualization or Multi row card visualization
Almost all visualizations fail to show results if the measures contain the SUMMARIZECOLUMNS DAX function. However, card or multi row card visualization shows the results.
The above cases are the cases when I use the SUMMARIZECOLUMNS DAX function. It is a little bit of a shame for me that I only have a few use cases while most people say this is the extremely powerful DAX function that is meant to be the "one function fits all".
I love to learn from readers what are your use cases of the SUMMARIZECOLUMNS DAX function.
I hope this helps to have fun with authoring DAX formulas using SUMMARIZECOLUMNS and NONVISUAL DAX functions.
Comments