cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bcdobbs
Super User
Super User

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 question has been marked as a solution but the conversation carried on and we've hit a an example which I can't explain so felt it best to open a new thread.

 

Based on @Strom original file I've put this together, which I think demo's the issue: Demo File 

 

There are two measures:

Sales Amount = 
    SUMX( 
        Sales, 
        Sales[Price]*Sales[Amount] 
    )

Sum Year = 
    SUMX( 
        VALUES( 'Calendar'[Year] ), 
        [Sales Amount] 
    )

 

Selecting a set like this:

bcdobbs_0-1641543520525.png

produces the following matrix:

bcdobbs_1-1641543549970.png

 

Now I would expect based on my understanding and a similar explanation in SQLBI's Correctly Compute Over Arbitrary Shaped Sets that Sum Year should be "wrong" but in a different way to the above.

 

Starting from (2019, 11), (2019, 12), (2020, 1), (2020, 2), (2020, 3), my expectation would be that sub total for 2019 would be 8 because the year filter is removed leaving just (1, 2, 3, 11, 12) and then 2019 is put back.

 

What appears to be happening is that for the 2019 sub total we get an answer based on the following filter:

(2019, 11) = 1

(2019, 12) = 1

(2020, 11) = 10

(2020, 12) = 10

 

If anyone could help explain this it would be very much appreciated ( @AlbertoFerrari , @marcorusso@AlexisOlson  ) 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
1 ACCEPTED SOLUTION

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.

View solution in original post

8 REPLIES 8

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.

Strom
Frequent Visitor

Thank you all (Benjamin, Marco, Alexis) for your time. The goal of my original question was not to find out how to get the right result. I know that. The goal was to understand how the evaluation of that query works. 

It would be great to find out more details about this behavior.

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:

bcdobbs_0-1641582394350.png


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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

It definitely does seem buggy.

 

If you rewrite with CONCATENATEX instead of SUMX, then the Year subtotals are different but the Total matches:

AlexisOlson_0-1641572891955.png

 

Using CONCATENATEX further as a debugger, we can see where Total comes from:

AlexisOlson_1-1641574220964.png

 

Can you think of a reason why SUMX and CONCATENATEX behave differently for the year subtotals?

 

the important thing is that you should use KEEPFILTERS around the table function in the iterator. Or iterate over a single column.

Can you expand on this or link to something to read? Does VALUES ( 'Calendar'[Date] ) not count as a single column since it requires KEEPFILTERS in this case?

It is probably because of the way SUM/SUMX is optimized for the subtotal, something that CONCATENATEX is not.

If you look at the storage engine queries, you should realize that there are two problems:

  1. The way the storage engine requests are composed (which is consistent with the behavior expected with SUMMARIZE/ADDCOLUMNS)
  2. The different behavior produced by the formula engine trying to "reuse" part of the data already computed to reduce the need for additional storage engine requests.

I didn't examine the CONCATENATEX query plan in detail, but I guess it does not have to generate SE queries for the intermediate totals. 

I'm not sure what you mean in the last question - can you elaborate?

 

Does VALUES ( 'Calendar'[Date] ) not count as a single column since it requires KEEPFILTERS in this case?

 

 

I think @AlexisOlson is referring to the end of your first reply:

 

"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."

 

In the Sum Year measure it is iterating over a single column. 

I assumed you'd actually meant, apply the filter with a single column eg Month-Year to avoid the arbitrary shapes set all together.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Yes correct - by iterating over month, you are iterating over the wrong granularity (12 months even though you have 2 years selected). While it is the same for a SUM, it wouldn't be the same for MIN/MAX/AVG.

If you pay attention to that, you do not have to use KEEPFILTERS. Using KEEPFILTERS could be a good idea in general, but it wouldn't save you when you have a non-additive aggregation function.

Strom
Frequent Visitor

Hi Benjamin,

thank you. 

Now I know why Italians love DAX. It's about love and hate and it's a relatively stable relationship 🙂

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!