topic Re: Cardinality confusion in a measure in DAX Commands and Tips

Cardinality confusion in a measure

MJS_00 — Thu, 21 Dec 2023 20:36:36 GMT

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.

Re: Cardinality confusion in a measure

OwenAuger — Fri, 22 Dec 2023 00:53:11 GMT

Hi MJS_00

Here's my explanation:

Iterators create row context but not filter context.
The result of any table expression is determined by the filter context in which the expression is evaluated. Row context does not in itself affect the result of a table expression.
The outer SUMX iterates over the rows of VALUES ( 'Product' ), and for a given row it creates row context (corresponding to a single row of 'Product') but not filter context.
Therefore, within the inner SUMX, the reference to the Sales table is within the same filter context in which the entire expression is evaluated. Nothing has been done to cause Sales to be evaluated in a different filter context.
If we wanted to apply the current row of VALUES ( 'Product' ) as an equivalent filter, we could make use of context transition (which means transforming row context into an equivalent filter context), which could be achieved by changing Sales to either:
- CALCULATETABLE ( Sales )
- RELATEDTABLE ( Sales )

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

Re: Cardinality confusion in a measure

MJS_00 — Fri, 22 Dec 2023 13:24:38 GMT

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

Re: Cardinality confusion in a measure

OwenAuger — Fri, 22 Dec 2023 23:39:08 GMT

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

Re: Cardinality confusion in a measure

MJS_00 — Sun, 24 Dec 2023 00:56:18 GMT

Thank you Owen & many thanks for the clarification on RELATEDTABLE vs CALCULATETABLE!!