Forum Discussion
Arbitrary Shaped Sets
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.
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.
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?
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?
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.
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.
