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.

Scenario: You have a concatenated field stored in a table. For example, a medical claim might have several denial reasons. Instead of representing this as a Many-to-Many relationship, you’ve decided to store this a comma-delimited field, such as to allow the user to see all codes on one row. However, users are asking to produce counts grouped by each reason code, such as this one:

Solution: Follow these steps to implement a DAX measure that dynamically parses the string.

1. Implement a DenialReason table with a single column DenialReason that stores the distinct reason codes. Add the table to your Power BI Desktop/Tabular model. Leaving it hanging without a relationship.
2. Add a CountByDenialReason DAX measure that parses the string:

   CountByDenialReason :=
   CALCULATE (
       SUMX (
   Claim,
           IF (
               NOT ISEMPTY (
                   FILTER (
                       VALUES ( DenialReason[DenialReason] ),
                       PATHCONTAINS (
                           SUBSTITUTE ( Claim[DenialReason], “,”, “|” ),
   DenialReason[DenialReason]
                       )
                   )
               ),
               1
           )
       )
   )

The NOT ISMPTY clause checks if the row contains any reasons. The PATHCONTAINS checks if the row has the denial reason whose context is passed from the report. In my case, the DenialReason field has a comma-delimited string if multiple reasons are stored on the row. Because PATHCONTAINS requires a pipe “|” delimiter, I use the SUBSTITUTE function to replace commas with pipes. If match is found for that reason, the IF operator returns one, which is summed in SUMX to return the actual count.

If you have a large table, you might get a better performance if you do the replacement in a wrapper SQL view or in Query Editor and avoid SUBSTITUTE altogether.