3.1.2. Relationships: Cardinality and Cross-Filter Direction
💡 First Principle: A relationship tells the engine which rows in one table correspond to rows in another and which way filters may travel between them. Cardinality describes how many rows match on each side; cross-filter direction describes whether the valve opens one way or both. Together they decide every number a cross-table visual produces.
| Cardinality | Meaning | Typical use | Notes |
|---|---|---|---|
| One-to-many (1:*) | Unique key on one side, repeated on many side | Dimension to fact | The default and best-performing choice |
| Many-to-one (*:1) | Same as above, viewed from the fact | Fact to dimension | Same relationship, other direction of definition |
| One-to-one (1:1) | Unique on both sides | Splitting one entity across tables | Often a sign the tables should be merged; defaults to both directions |
| Many-to-many (*:*) | Neither side unique | Facts at different grains (budget by month and category vs. sales by day and product) | Can produce unexpected totals; use deliberately |
Cross-filter direction is Single (filters flow from the one side to the many side) or Both (bidirectional). Single is the default for one-to-many and the recommended setting. Both lets filters on the fact (or one dimension) affect another dimension through the fact. Legitimate uses include bridging tables in many-to-many patterns (customers with multiple accounts) and letting a dimension slicer show only members with data, but Both has costs: it can create ambiguous paths when multiple routes connect two tables (Power BI blocks or deactivates relationships that would cause ambiguity), it slows queries, and it can make totals hard to explain. A narrower alternative is enabling bidirectional filtering for one measure only with CROSSFILTER(Table1[Key], Table2[Key], Both) inside CALCULATE.
For row-level security, a relationship with Both direction has an extra option, Apply security filter in both directions, which lets RLS filters propagate across that relationship in both directions; without it, security filters flow one way even on bidirectional relationships.
Other relationship settings to recognize: Make this relationship active, and Assume referential integrity (DirectQuery only: lets Power BI use inner joins instead of outer joins for faster queries when every fact key is guaranteed to match).
Power BI creates relationships automatically (autodetect) when column names and types match; always review them, because autodetect can relate the wrong columns or choose the wrong cardinality.
⚠️ Exam Trap: Enabling bidirectional filtering on every relationship "so slicers work everywhere" is a classic wrong answer. It creates ambiguity and performance problems. The preferred answers are a single-direction star schema, a measure-based filter on a slicer, or CROSSFILTER scoped to one measure.
Reflection Question: A Budget table at Month and Category grain must be compared with a Sales table at Day and Product grain. Why can't Budget relate to Date and Product with ordinary one-to-many relationships, and what two modeling options handle the mismatch?
