cancel
Showing results for
Did you mean:
Super User

Arbitrary Shaped Sets

Hi,

I've been discussing evaluation contexts particularly with regard to arbitrary shaped sets with @Strom on this thread:

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:

produces the following matrix:

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

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

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.

8 REPLIES 8
MVP

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.

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.

Super User

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 (
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!)

Ben Dobbs

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

It definitely does seem buggy.

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

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

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?

MVP

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?

Super User

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

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

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.

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 🙂

Announcements

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 Design Challenge

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

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.