topic Re: Sequence of Applied Filter Context in SUMMARIZECOLUMNS in DAX Commands and Tips

Sequence of Applied Filter Context in SUMMARIZECOLUMNS

Anonymous — Fri, 05 May 2023 15:33:15 GMT

Hello, I'm struggling to understand the order of operations for how filters are being applied by SUMMARIZECOLUMNS. From the content in the DGTD, we're given the idea that the behavior of SUMMARIZECOLUMNS in how it applies the FilterTable argument is somewhat similar to how CALCULATE works. However, I haven't seen any delineated sequence of behavior for how filters are applied, like what we have for CALCULATE.

An example based on Contoso is below and can be run in dax.do. Specifically, in "Sales with Calculate" below, it seems that, in the first row of the resulting table, CALCULATE's context transition is causing "Red" & "White" to overwrite "Red" in Product[Color]. This is a familiar concern especially when working with arbitrarily shaped sets. However, I'm struggling to understand how VALUES ( Product[Color] ) is able to see both colors in the context, given that the GroupBy_ColumnName only contains one color per row and also given the fact that the similar clause ( VALUES ( Product[Color] ) ) in "Sales Amount" apparently only retrieves one color per row.

What am I missing? Any tips are greatly appreciated!

Thanks. 🙂

// This is the query I'm struggling with. EVALUATE SUMMARIZECOLUMNS ( 'Product'[Color], TREATAS ( { ( "CY 2008", "Red" ), ( "CY 2007", "White" ) }, 'Date'[Calendar Year], 'Product'[Color] ), "Sales Amount", SUMX ( VALUES ( Product[Color] ), SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) ), "Sales With Calculate", SUMX ( VALUES ( Product[Color] ), CALCULATE ( SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) ) ) ) ORDER BY [Sales Amount] DESC // This query for reference to see the values being combined EVALUATE SUMMARIZECOLUMNS ( 'Product'[Color], 'Date'[Calendar Year], TREATAS ( { "CY 2008", "CY 2007" }, 'Date'[Calendar Year] ), TREATAS ( { "Red", "White" }, 'Product'[Color] ), "Sales Amount", SUMX ( VALUES ( Product[Color] ), SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) ), "Sales with Calculate", SUMX ( VALUES ( Product[Color] ), CALCULATE ( SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) ) ) )

Re: Sequence of Applied Filter Context in SUMMARIZECOLUMNS

johnt75 — Fri, 05 May 2023 15:56:39 GMT

SQLBI have a great video explaining this - https://www.youtube.com/watch?v=bGVLguWf4Ls .

If you use KEEPFILTERS you get the correct result

EVALUATE SUMMARIZECOLUMNS ( 'Product'[Color], TREATAS ( { ( "CY 2008", "Red" ), ( "CY 2007", "White" ) }, 'Date'[Calendar Year], 'Product'[Color] ), "Sales Amount", SUMX ( VALUES ( Product[Color] ), SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) ), "Sales With Calculate", SUMX ( VALUES ( Product[Color] ), CALCULATE ( SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) ) ), "Sales With Calculate KEEP", SUMX ( KEEPFILTERS(VALUES ( Product[Color] ) ), CALCULATE ( SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) ) ) ) ORDER BY [Sales Amount] DESC

Re: Sequence of Applied Filter Context in SUMMARIZECOLUMNS

Anonymous — Fri, 05 May 2023 16:01:25 GMT

Thanks John, yes, that's a great video. However, my question is about how SUMMARIZECOLUMNS is creating the values we see.

I can't explain how we can get "Sales with Calculate," and that's what I'm trying to understand.

Thanks.

Re: Sequence of Applied Filter Context in SUMMARIZECOLUMNS

johnt75 — Fri, 05 May 2023 16:30:51 GMT

I think its the same as in the video. When the White row is being evaluated the filter context contains 2007 and white. Somehow, and I can't quite put my finger on how, applying VALUES is overriding the white part of the filter context with both white and red, but it is leaving 2007 in the filter context, so the numbers shown are the values for white & red in 2007. Similarly, for red the values shown are for white and red in 2008.

I can't quite get how VALUES is getting the outer filter context, containing white & red, when white alone is in the filter context.

Re: Sequence of Applied Filter Context in SUMMARIZECOLUMNS

Anonymous — Fri, 05 May 2023 23:04:01 GMT

Yes, somehow, the presence of CALCULATE seems to be inflencing what VALUES can see in the context (I don't think that's what's happening, but that's just how it looks.). This is wha I can't wrap my head around.

Re: Sequence of Applied Filter Context in SUMMARIZECOLUMNS

Anonymous — Fri, 05 May 2023 23:05:37 GMT

marcorusso, any insight you can provide? 🙂

Re: Sequence of Applied Filter Context in SUMMARIZECOLUMNS

marcorusso — Sat, 06 May 2023 05:08:18 GMT

Read the article! 😉

When to use KEEPFILTERS over iterators - SQLBI

Re: Sequence of Applied Filter Context in SUMMARIZECOLUMNS

Anonymous — Thu, 15 Jun 2023 20:13:22 GMT

Per Alberto Ferrari, the behavior observed and described here is due to a bug in the optimizer. See the comments on the page here.