Forum Discussion

bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

Arbitrary Shaped Sets

Hi, I've been discussing evaluation contexts particularly with regard to arbitrary shaped sets with Strom on this thread: Evaluation context - mixed filter and row context    The original questio...
  • marcorusso's avatar
    marcorusso
    4 years ago

    The description we provide in the book applies to MDX queries and to other scenarios like those obtained using the context transition in a filter context managed by SUMMARIZE and/or ADDCOLUMNS.

    Power BI uses a function (SUMMARIZECOLUMNS) that introduces another behavior (could it be called a bug on top of another bug?) which generates results that are less clear and predictable. We never described in detail what happened, because the important thing is that you should use KEEPFILTERS around the table function in the iterator. Or iterate over a single column.

marcorusso's avatar
marcorusso
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

The description we provide in the book applies to MDX queries and to other scenarios like those obtained using the context transition in a filter context managed by SUMMARIZE and/or ADDCOLUMNS.

Power BI uses a function (SUMMARIZECOLUMNS) that introduces another behavior (could it be called a bug on top of another bug?) which generates results that are less clear and predictable. We never described in detail what happened, because the important thing is that you should use KEEPFILTERS around the table function in the iterator. Or iterate over a single column.

bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

Thanks marcorusso really appreciate you taking time to reply! I hadn't considered that it was an interaction with the SUMMARIZECOLUMNS from the matrix that was causing the issue! Realise the use of KEEPFILTERS or using a more descriptive single column to form the filter with is the way forward; this was more of an academic exercise in understanding.

 

For those interested I copied the DAX that the matrix forms and rewrote it using SUMMARISE/ADDCOLUMNS in DAX Studio connected to the demo file:

 

DEFINE

    VAR FilterTable =
        TREATAS (
            {
                ( 2019, 11 ),
                ( 2019, 12 ),
                ( 2020, 1 ),
                ( 2020, 2 ),
                ( 2020, 3 )
            },
            'Calendar'[Year],
            'Calendar'[Month]
        )
        
    VAR ResultTable =
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE (
                    Sales,
                    'Calendar'[Year]
                ),
                "Sum Year", 'Sales'[Sum Year]
            ),
            FilterTable
        )

EVALUATE

ResultTable

 This does indeed produce the expected "wrong" results:


I thought I'd made peace with this in my head until I saw AlexisOlson response below! (loving the use of COCATENATEX for debugging!)