DAX expressions returning different results (CALCULATE vs CALCULATETABLE)

[jfbonterra]

Hello,

I encountered confusing results when creating measures using DAX calculations. It appears I’m getting different outputs when using CALCULATE vs CALCULATETABLE in certain circumstances. I’m not getting the expected output when using CALCULATE. I’m adding filter context on both measures and the filter context is the same. Details below.

Measures used:

SAO Opportunities = CALCULATETABLE(

    ROW ("SAO Opportunities", COUNTROWS(Opportunity) ),

        KEEPFILTERS ( TREATAS ( {"true"}, 'Opportunity'[SAO] )),

        USERELATIONSHIP ( 'Date'[Date], Opportunity[SAO Date])

        )

SAO Opportunities (Calculate function) = CALCULATE ( COUNTROWS ( Opportunity ),

        FILTER ( Opportunity, Opportunity[SAO] = "true" ),

        USERELATIONSHIP ( 'Date'[Date], Opportunity[SAO Date] )

        )

Output:

For further context, there is an inactive relationship set up for the Date table with the Opportunity SAO Date field. There is a column in the Opportunity table called SAO and it’s a text field.

When I filter the data in the Data View section to meet the above criteria, here is the output (matches output from CALCULATETABLE):

Is anyone able to provide insight into the discrepancy?

Thanks!

[jfbonterra]

Thank you for the detailed explanation!

[OwenAuger · Accepted solution]

Hello @jfbonterra 

I will attempt to give some explanation, but a model diagram would be useful to be sure of the explanation.

Firstly, just restating your two measures for reference:

SAO Opportunities =
CALCULATETABLE (
    ROW ( "SAO Opportunities", COUNTROWS ( Opportunity ) ),
    KEEPFILTERS ( TREATAS ( { "true" }, 'Opportunity'[SAO] ) ),
    USERELATIONSHIP ( 'Date'[Date], Opportunity[SAO Date] )
)

SAO Opportunities (Calculate function) =
CALCULATE (
    COUNTROWS ( Opportunity ),
    FILTER ( Opportunity, Opportunity[SAO] = "true" ),
    USERELATIONSHIP ( 'Date'[Date], Opportunity[SAO Date] )
)

The key difference between these measures is the way in which the Opportunity[SAO] filter is applied: a column filter vs a table filter.

1. KEEPFILTERS ( TREATAS ( { "true" }, 'Opportunity'[SAO] ) ) applies a "column filter" to the single column Opportunity[SAO], intersected with existing filters.
2. FILTER ( Opportunity, Opportunity[SAO] = "true" ) applies a "table filter" to Opportunity (filtered to SAO = "true"). A table filter is actually a filter on the expanded table, which includes all columns of the Opportunity table itself and related tables on the 1-side of any 1:many relationships with Opportunity.

The reason I suspect the 2nd measure gives an unexpected result is that FILTER(...) returns the expanded Opportunity table (with filter applied), which includes columns of 'Date' (I'm assuming there is an active relationship between Opportunity and 'Date'). When USERELATIONSHIP is applied to modify the relationship, the 'Date' rows that were originally related to Opportunity rows are applied as filters using the modified relationship. This doesn't happen with the first measure.

The upshot of this is I recommend using a column filter with KEEPFILTERS as in your 1st measure, as table filters can have unintended side effects due to table expansion.

To quote The Definitive Guide to DAX:

"Using table filters is always challenging because of table expansion. Whenever one applies a filter to a table, the filter is really applied to the expanded table, and this can cause several side effects. The golden rule is simple: Try to avoid using table filters whenever possible. Working with columns leads to simpler calculations, whereas working with tables is much more problematic."

Regards