Sequence of Applied Filter Context in SUMMARIZECOLUMNS

[dskrobowski]

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

[dskrobowski · Accepted solution]

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

[marcorusso]

Read the article! 😉

When to use KEEPFILTERS over iterators - SQLBI

[johnt75]

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

[dskrobowski]

@marcorusso, any insight you can provide? 🙂

[dskrobowski]

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.

[johnt75]

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.

[dskrobowski]

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.