CALCULATE is the most powerful function in DAX, as it allows you to change the filter context under which its expression is evaluated to your hearts content. But with big number of options to choose from, often comes big frustration when the results don’t match expectations. Often this is because your syntax to modify the filter context doesn’t do what you’ve intended. Unfortunately CALCULATE only displays its result and not how it achieved it, so debugging becomes a challenge. This is where my CALCULATE Debugger measure can help out:
DAX CALCULATE Debugger
This is a measure that returns a text-value, showing the number of rows of the adjusted filter context table, the MIN and MAX value of the selected column as well as up to the first 10 values. Just place this measure beneath the CALCULATE-measure in question and try to find the error 😉
Just have in mind, that this only works for standalone CALCULATE-functions and not for those who are nested in other functions (who modify the evaluation context).
The YTD-measure is defined as follows:
YTD = CALCULATE ( [Amount], DATESYTD ( 'DimDate'[Datum] )
The code for the DAX Debugger measure looks like this:
In row 2 you fill in the filter expression from the YTD-expression (2nd argument: ‘DimDate'[Datum]). You can choose from which column the values shall be shown, just write that in rows 6, 7 and 11 ([Datum]). If you want to adjust the TOPN-figure for the sample values to be shown, replace the 10 in row 9 accordingly. If you don’t want to show sample values at all, just uncomment row 13 and comment out row 14 and 15.
Thanks to Tom Martens for providing the crucial hint of how to reference a column from a table that’s defined in a variable by using X-functions!
Further adjustments have to be made, if your filter expression uses the syntax sugar of boolean expressions like so:
CALCULATE ( [Amount], Product[Color] = "Red" )
This expression only returns a table when used as a filter argument in CALCULATE but not standalone in a DAX variable. So you’d have to translate the filter expression to the native underlying code like so:
FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" )
As this cries for some automation, I’ve produced some nifty M-function that does all that autoMagically. It lives in my M-function-library so I have it at hand within PowerBI for immediate use.
This function creates the DAX-code in the query editor. Just fill in the parameters (see below) and the DAX code will be created automatically: Just copy and paste as a new measure.
How to fill the parameters:
- filterExpression: DAX-code of the CALCULATE filter expression
- myColumName. Name of the column whose values to show
- MaxFilter: This is a optional parameter: Fill in a different number from 10 if you want to change the default value for the TOPN selection of the sample values to be shown.
This function detects boolean expressions automatically and produces the appropriate code.
If you don’t know how to use M-function-code, please check out Ruth Pozuelo’s video.
Enjoy & stay queryious 😉