Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am confused by the cardinality of this measure which appears on page 189 of the Definitive Guide to Dax 2nd edition:
SalesAmoutHighCardinality =
SUMX(
VALUES('Product'),
SUMX(
Sales,
IF(
Sales[ProductKey] = 'Product'[ProductKey],
'Product'[Unit Price]* Sales[Quantity],
0
)
)
)
The data model is the standard Contoso model with a 1 to many relationship between Product & Sales.
The book states that "the inner SUMX always iterates over the whole Sales table, relying on the internal IF statement to check whether the product should be considered or not for the calculation. In this case the outer SUMX has the cardinality of Product, whereas the inner SUMX has the cardinality of Sales. The cardinality of the whole expression is Product times Sales."
My question is why does the inner SUMX iterate over the whole Sales table when I would assume it would be evaluated in the row context of the outer SUMX on Product (thereby filtering the Sales table for the currently iterated Product).
I understand this is a grossly inefficient construct, this question is just for my understanding. Any help here would be greatly appreciated.
Solved! Go to Solution.
Hi @MJS_00
Here's my explanation:
It looks like page 190 of book addresses "Leveraging context transition in iterators" which should help with understanding this.
Also see:
https://www.sqlbi.com/articles/understanding-context-transition-in-dax/
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
https://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html
Hopefully that helps! Post back if needed 🙂
Regards,
Owen
@MJS_00 You're welcome 🙂
Yes, the external filter context would apply to the measure as a whole, so Sales or Product would be evaluated in that external filter context. I guess the authors are assuming no filters have been applied for the purpose of that discussion.
I personally prefer CALCULATETABLE, as it makes it easier to identify instances of context transition in a consistent way by looking for CALCULATE/CALCULATETABLE or measure references.
RELATEDTABLE ( tbl ) is equivalent to CALCULATETABLE ( tbl ) where tbl is a physical table. However, CALCULATETABLE allows for further filter arguments.
(see here)
Regards
@MJS_00 You're welcome 🙂
Yes, the external filter context would apply to the measure as a whole, so Sales or Product would be evaluated in that external filter context. I guess the authors are assuming no filters have been applied for the purpose of that discussion.
I personally prefer CALCULATETABLE, as it makes it easier to identify instances of context transition in a consistent way by looking for CALCULATE/CALCULATETABLE or measure references.
RELATEDTABLE ( tbl ) is equivalent to CALCULATETABLE ( tbl ) where tbl is a physical table. However, CALCULATETABLE allows for further filter arguments.
(see here)
Regards
Thank you Owen & many thanks for the clarification on RELATEDTABLE vs CALCULATETABLE!!
Hi @MJS_00
Here's my explanation:
It looks like page 190 of book addresses "Leveraging context transition in iterators" which should help with understanding this.
Also see:
https://www.sqlbi.com/articles/understanding-context-transition-in-dax/
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
https://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html
Hopefully that helps! Post back if needed 🙂
Regards,
Owen
Hi Owen,
Many thanks for your detailed answer, it makes much more sense now. I have a couple of follow up points:
I am assuming that the external filter context would still apply when this measure was executed which would result in a appropriately filtered Sales table. If so then the total cardinality in reality would never be as high as Product * Sales. If this is correct, saying this measure has a total cardinality of Product * Sales is more of a standalone theoretical way of just looking at the measure itself.
Regarding your point on introducing filter context via CALCULATETABLE or RELATEDTABLE. Is there a generally perference for one over the other?
Many thanks for your help.
Matt
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |