topic Re: Arbitrary Shaped Sets in DAX Commands and Tips

Arbitrary Shaped Sets

bcdobbs — Fri, 07 Jan 2022 08:27:14 GMT

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 )

Re: Arbitrary Shaped Sets

Strom — Fri, 07 Jan 2022 11:53:12 GMT

Hi Benjamin,

thank you.

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

Re: Arbitrary Shaped Sets

marcorusso — Fri, 07 Jan 2022 15:29:40 GMT

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.

Re: Arbitrary Shaped Sets

AlexisOlson — Fri, 07 Jan 2022 16:54:15 GMT

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?

Re: Arbitrary Shaped Sets

bcdobbs — Fri, 07 Jan 2022 19:10:21 GMT

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

Re: Arbitrary Shaped Sets

Strom — Fri, 07 Jan 2022 20:12:00 GMT

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.

Re: Arbitrary Shaped Sets

marcorusso — Sat, 08 Jan 2022 08:22:09 GMT

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:

The way the storage engine requests are composed (which is consistent with the behavior expected with SUMMARIZE/ADDCOLUMNS)
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?

Re: Arbitrary Shaped Sets

bcdobbs — Sat, 08 Jan 2022 08:50:09 GMT

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.

Re: Arbitrary Shaped Sets

marcorusso — Sat, 08 Jan 2022 14:18:43 GMT

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.

Re: Arbitrary Shaped Sets

Zx2000 — Sun, 19 Mar 2023 10:08:15 GMT

Same here, I spent 3 days tryging to figure out what is happening on the subsume level but the logic for totals does not match for subtotals.
As far as I understand (which is different from your idea) is that the filter context for 2019 matrix row is 2019, 11 and 2019, 12 therefore Values(year) should iterate only for 2019 (as this is the only year from context coming from VALUES(year), isn't it? Why do you expect to have all months there as filter context for this row is 11 and 12 only ?
Due to context transition (iteration over only 2019) - I would expect to 2019 become a new filter which should chenge the original one for this column (which is the same - 2019) -> therefore the subsume should be calculated in a correct way(2019, 11 and 2019 12).
But the result for the 2019 subsume is year = 2019, 2020 and month = 11,12

(2019/11, 2019/12, 2020/11, 2020,12)