DAX Variables to Rescue

Scenario: DAX has its own share of idiosyncrasies that can humble both novice and experienced users. Consider a common example where a measure attempts to return sales for the last date in the Date table. What makes this common is that many real-life calculations require measures that evaluate as of the user-specified date (as of date), which is what MAX(‘Date'[Date]) returns.

=CALCULATE(SUM(Sales[SalesAmount]), MAX(Date[CalendarYear]) )

As innocent and logically correct this measure is, it fails with “A function ‘MAX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.” It’s also amusing to see even Microsoft struggling to explain the reason of this error which at some point was on the internal top 10 Microsoft list for DAX “ease of use”. This is what the documentation states about this error:

“The filter expression, MAX(‘Date'[CalendarYear]) attempts to return the largest numeric value in the CalendarYear column. However, in context of the measure expression, it cannot be passed as a table filter expression to the CALCULATE function, causing an error.”

This of course is incorrect. First, the MAX function doesn’t return a table but a scalar value. Second, the CALCULATE function is perfectly capable of taking Boolean expressions. The actual issue is that the Boolean expression is surrounded by a hidden CALCULATE and it’s ambiguous in what context the maximum date should be evaluated. To be consistent with the way filters propagate, it should be in the filter context outside of CALCULATE, but in the row context of the as-of date, which becomes a filter context with MAX expression. But this is not what you would expect, so DAX fails safe with the error.

The workaround suggested by the documentation is to filter the Date table and pass it as a table filter to calculate. This requires ignoring first the filter context on the Date table, only to overwrite it later with the ‘as of date’.

=CALCULATE( SUM(Sales[SalesAmount]), FILTER( ALL( ‘Date’[CalendarYear]), [CalendarYear] = MAX(‘Date’[CalendarYear]) ) )

Solution: A better solution is to use a variable. This example defines an EOP (End of Period) variable.

= VAR EOP = MAX(Date[CalendarYear]) RETURN CALCULATE(SUM(Sales[SalesAmount]), [CalendarYear] = EOP )

Because the EOP variable is evaluated where it’s defined there is no hidden context and the measure works. Unfortunately, as it stands today, DAX doesn’t allow us to create a global session variable for such scenarios. So, you’d need to include this variable in every measure that requires it.

Speaking of issues with the DAX documentation and variables, here is another example (last example on that page) that doesn’t work:

YoY% = var Sales = SUM(SalesTable[SalesAmount]) var SalesLastYear=CALCULATE(Sales, SAMEPERIODLASTYEAR(‘Calendar‘[Date])) return
if(Sales, DIVIDE(Sales – SalesLastYear, Sales))

This doesn’t work because the SalesLastYear var attempts to overwrite the context of the Sales var. In other words, it attempts to treat it as a measure but SalesLastYear would always return this year sales. The correct example should be:

YoY% = var Sales = SUM(SalesTable[SalesAmount]) var SalesLastYear=CALCULATE(SUM(SalesTable[SalesAmount]), SAMEPERIODLASTYEAR(‘Calendar‘[Date])) return
if(Sales, DIVIDE(Sales – SalesLastYear, Sales))

The documentation should say that although one variable can reference another, it can’t overwrite its context. Also, to avoid a logical bug, the example should actually be:

YoY% = var Sales = SUM(SalesTable[SalesAmount]) var SalesLastYear=CALCULATE(SUM(SalesTable[SalesAmount]), SAMEPERIODLASTYEAR(‘Calendar‘[Date])) return
if(AND(Sales, SalesLastYear), DIVIDE(Sales – SalesLastYear, Sales))

Because it’s meaningless to calculate YoY if this year or last year sales are missing.