Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jfbonterra
Frequent Visitor

DAX expressions returning different results (CALCULATE vs CALCULATETABLE)

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:

 

jfbonterra_0-1682606709585.png

 

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):

jfbonterra_1-1682606709607.png

 

Is anyone able to provide insight into the discrepancy?

 

Thanks!

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
jfbonterra
Frequent Visitor

Thank you for the detailed explanation!

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors