cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexisOlson
Community Champion
Community Champion

Understanding Context Transition with Table Filter Arguments

As a follow-up to this post, I've created a simpler example that demonstrates the core of the behavior I still don't quite understand.

 

Consider the following table, 'Data':

 

Data = DATATABLE ( "group", STRING, "amount", INTEGER, { {"A", 5}, {"A", 10}, {"B", 12}, {"C", 15} } )

 

AlexisOlson_0-1626811467229.png

 

I'd like to understand why the following do not give the same result:

 

SUMMARIZECOLUMNS (
    Data[group],
    "Rank", RANKX ( ALL ( Data[group] ), CALCULATE ( SUM ( Data[amount] ) ) )
)

 

versus

 

SUMMARIZECOLUMNS (
    Data[group],
    Data,
    "Rank", RANKX ( ALL ( Data[group] ), CALCULATE ( SUM ( Data[amount] ) ) )
)

 

The first gives A,B,C with Rank 2,3,1 respectively, whereas the second gives Rank 1,1,1.

 

Similarly, the following also return different results (which match the results above):

 

ADDCOLUMNS (
    VALUES ( Data[group] ),
    "Rank",
        CALCULATE (
            RANKX ( ALL ( Data[group] ), CALCULATE ( SUM ( Data[amount] ) ) )
        )
)

 

versus

 

ADDCOLUMNS (
    VALUES ( Data[group] ),
    "Rank",
        CALCULATE (
            RANKX ( ALL ( Data[group] ), CALCULATE ( SUM ( Data[amount] ) ) ),
            Data
        )
)

 

 

Curiously, if I replace RANKX with SUMX in the ADDCOLUMNS pair of expressions, the results match each other (both give A,B,C with Rank 47,47,47) whereas the SUMMARIZECOLUMNS pair gives A,B,C with Rank 47,47,47 for the first expression but Rank 15,12,20 for the second one.

 

These examples are a bit contrived but I'm looking to understand exactly why these behave the way they do and this was the most simplified form I could come up with. They may look slightly less odd if you replace CALCULATE ( SUM ( Data[amount] ) ) with a  measure [SumAmount] := SUM ( Data[amount] ).

 

What's going on here? How is the table argument getting applied in these cases?

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

I believe this is Auto-Exist kicking in. SUMMARIZECOLUMNS combines all filters from the same table into one filter, so in your second evaluation, you have DATA, and DATA[Group] - and they get combined since it is the same table. 

Take a look at the detailed explanation of SUMMARIZECOLUMNS() here and then the Auto-Exist issue you may need to contend with here.

Auto-exist is a pain to recon with. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
wdx223_Daniel
Super User II
Super User II

can this help you?

wdx223_Daniel_0-1626839647243.png

think you can get more from here

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

edhans
Super User III
Super User III

I believe this is Auto-Exist kicking in. SUMMARIZECOLUMNS combines all filters from the same table into one filter, so in your second evaluation, you have DATA, and DATA[Group] - and they get combined since it is the same table. 

Take a look at the detailed explanation of SUMMARIZECOLUMNS() here and then the Auto-Exist issue you may need to contend with here.

Auto-exist is a pain to recon with. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

AlexisOlson
Community Champion
Community Champion

Thanks. Auto-Exists does seem like the likely explanation, at least for the SUMMARIZECOLUMNS cases since using a dimension table for group resolves the issue.

 

For the ADDCOLUMNS examples, using a dimension table doesn't seem to help so maybe there's something else happening. Auto-Exists also doesn't explain why RANKX and SUMX behave differently for these. 

I'm certianly not an expert in Auto-Exists, but I believe it returns a table with specific info, therefore SUMX and RANKX are operating on a different result set than the SUMMARIZECOLUMNS without the filter table. That is why you are getting different results. SQLBI does a deep dive into Auto Exist in this video.

If you could mark one or more of these as the solution so this thread can be marked solved, we'd appreciate it.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
AlexisOlson
Community Champion
Community Champion

Why would SUMX operate on a different result set than RANKX in the ADDCOLUMNS examples (where the results match each other using SUMX but don't match using RANKX)?

 

My only guess would be that there might be some kind of internal optimization such that SUMX isn't actually behaving as an iterator whereas RANKX still must.

SUMX is always an iterator. To really trouble shoot this you'd need to analyze the query plan in DAX Studio to see what the engine is doing. That will show you the exact pseudo-SQL being generated for each query and how they differ.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.