TL;DR: This generic, portable DAX query flags relationships in import models where the data supports a more efficient cardinality than is currently set, like many-to-one or one-to-one.
Inherited semantic models often contain many-to-many relationships and data you aren't familiar with. You know query performance is worse with many-to-many relationships, but checking by hand whether the data supports a more efficient join is tedious.
So I wrote a dynamic, generic DAX query that tests whether the data supports a more efficient join than is currently set in the semantic model. It is built on two DAX metadata functions, so it executes quickly and is totally portable across semantic models, with no customization needed to run it:
- INFO.VIEW.RELATIONSHIPS() returns how each relationship is currently set, including its cardinality.
- COLUMNSTATISTICS() returns the distinct value count for each column and the row count for each table. If a key's distinct values equal its table's rows, the key is unique.
Besides the portability, I also love that this is basically a performance tuning freebie. Most optimizations have tradeoffs for end users, like removing data or adding friction like apply buttons, but not this.
Above is a preview of the query's results. The top two rows are many-to-many relationships where the key on one side is actually unique (3 distinct values in 3 rows, and 57 in 57).
- The first column, Recommendation, flags every relationship where the cardinality setting differs from what the data supports, and an ORDER BY sorts those exceptions to the top.
- The last column is an optional check, covered below.
I keep the query saved in my template reports in DAX Query View, where it's useful for performance audits, validating relationships in a conversion from a platform without cardinality info, and confirming or learning the grain of a model someone else built.
A few caveats:
- It only works for import models.
- It only considers the current state of the data. Duplicates may be valid in the source and show up later.
- Suggested changes will likely work but aren't guaranteed, because of one rare exception involving blanks, covered next.
relationship-cardinality-audit.dax (.dax, but really a plain text file)
Read on only if you are a true nerd about things like this, though I recommend at least the first bullets to better understand the platform.
Optional: check for blanks before setting a side to One
DID YOU KNOW?
- BLANK (null) is considered a distinct value in DAX's DISTINCTCOUNT and COLUMNSTATISTICS(). That's why the DISTINCTCOUNTNOBLANK alternative exists, which is usually what people intend.
- Any blank/null in a key column prevents you from setting that side of the relationship as a "One":
Thus, a key column that is fully distinct and includes a single blank will look like a candidate for the "One" side to this query, but Power BI will reject the change.
This is rare, and if you try the change, Power BI blocks it with a clear error, so I treat the check as optional. For rows with a suggested change, the last column in the included query includes a DAX validation query you can copy/paste and run that tests this constraint and returns "Proceed" or "Do not proceed." To run it, use DAX Studio rather than DAX Query View, because the latter adds noisy escape characters when you copy the cell with the query in it.