skip to content

In Power BI, what do a relationship's cardinality and cross-filter direction control?

level: middleimportance: must knowfreq 75%

answer

  1. two settings, not one
  2. one says where keys are unique
  3. the other says which way filters walk
  4. dimensions describe facts, so filters run downhill
  5. a slicer on the fact may not shrink the dimension

basics

~20 s

Cardinality declares which side of the relationship holds unique key values; cross-filter direction declares which way a filter travels along it. A one-to-many relationship filters from the one side down to the many side, and by default not back.

solid answer

~50 s

Every Power BI relationship joins one column in each table and carries two properties. **Cardinality** — one-to-many, many-to-one, one-to-one or many-to-many — states where the key is unique; in a star schema the dimension is the one side and the fact table is the many side. **Cross-filter direction** — Single or Both — states which table's filters propagate to the other. With the default Single on a one-to-many relationship, filters flow *down* from the dimension to the fact: selecting a product filters sales. They do not flow *up*, so filtering sales does not shrink the product list, and a visual counting product rows ignores your sales slicer. That asymmetry is deliberate: a dimension describes many facts, so filtering upward is ambiguous and, once several relationships are involved, can create more than one path between tables.

code

text · 7 lines
text
Date[Date]          1 --> *  Sales[OrderDate]     single direction
Product[ProductKey] 1 --> *  Sales[ProductKey]    single direction
Customer[CustKey]   1 --> *  Sales[CustKey]       single direction

-- filters travel dimension -> fact only
-- slicer on Product[Category] filters SUM(Sales[Amount])   : yes
-- slicer on Sales[Channel]   filters COUNTROWS(Product)    : no

go deeper

for a junior

Be ready to point at a model diagram and say which table is the one side and which is the many side, and that a dimension slicer filters the fact table by default.

for a middle

Explain propagation itself: filters land on columns, travel along active relationships in the permitted direction, and only then does aggregation happen. Give the example of a fact-table slicer failing to shrink a dimension visual.

for a senior

Diagnose with it. Show how you trace a wrong number back to a missing, inactive or wrongly-directed relationship, and when you would scope propagation to one measure instead of changing the model.

for a principal

Own the modelling standard: single-direction star relationships as the default, limited relationships and composite-model paths as reviewed exceptions, so self-service authors inherit a model where only one answer is possible.

## The two properties on every relationship In Power BI a relationship connects exactly one column in one table to one column in another. Beyond the columns, it carries two settings that decide everything about how numbers land in visuals. **Cardinality** describes uniqueness. `One-to-many` (shown `1` on one end and `*` on the other) means values in the one-side column are unique and values on the many side may repeat. In a star schema this is the normal shape: `Date`, `Product`, `Customer` are one-side dimensions; `Sales` is the many-side fact table. `Many-to-one` is the same relationship read from the other table. `One-to-one` means both columns are unique — usually a sign two tables should have been merged. `Many-to-many` cardinality allows both columns to contain duplicates and is a different animal, discussed below. **Cross-filter direction** describes propagation: which table's filters travel across the relationship. `Single` (the default for one-to-many) means filters travel only from the one side to the many side. `Both` means filters travel in either direction. For a one-to-one relationship the direction is always both, because there is no ambiguity. ## What "filters travel" actually means When a visual is rendered, the filters on it — slicers, the row and column headers, page and report filters — are applied to the columns they belong to and then *propagate* along active relationships in the permitted direction. Only after propagation does the engine aggregate the fact table. So in a model with `Date[Date] 1 -> * Sales[OrderDate]` and `Product[ProductKey] 1 -> * Sales[ProductKey]`, both with Single direction: - A slicer on `Product[Category] = "Bikes"` filters `Product` to bike rows, then propagates down to `Sales`, leaving only bike sales rows for `SUM(Sales[Amount])` to add up. Correct. - A slicer on `Sales[Channel] = "Online"` filters `Sales`. It does **not** propagate up to `Product`, so a card showing `COUNTROWS(Product)` still counts every product, including ones never sold online. This surprises people constantly and is not a bug. That second case is the interview point. Filtering upward is not free: a product row matches many sales rows, so "which products survive this sales filter" is a real question the engine can answer only if you ask it to, by setting the direction to Both or by scoping it inside a measure with `CROSSFILTER`. ## Active versus inactive Between any two tables only **one** relationship path may be active at a time. If you create a second relationship — say `Date[Date]` to both `Sales[OrderDate]` and `Sales[ShipDate]` — Power BI makes the second one inactive (drawn as a dashed line) and ignores it during normal propagation until a measure activates it. Power BI also refuses to create a relationship that would make the active paths ambiguous or circular, and tells you to make it inactive instead. Both rules exist to guarantee there is exactly one way a filter can reach a table, because two paths would mean two possible answers. ## Many-to-many cardinality is not the same kind of relationship The `many-to-many` cardinality option lets you relate two tables directly on a column that repeats on both sides — for example relating a `Sales` table to a `Budget` table on `Month`. It is convenient but it creates what the engine calls a *limited* (weak) relationship rather than a *regular* (strong) one. Limited relationships behave differently: no blank row is added on the one side for unmatched keys, `RELATED` cannot traverse them from row context, and unmatched values simply drop out of cross-filtered results, which is how totals start disagreeing with the sum of the rows. Relationships that cross source groups in a composite model are limited for the same reason. Treat many-to-many cardinality as a deliberate choice, not a shortcut around modelling. ## How to reason about a model you are handed Read the diagram as a set of arrows. Each arrowhead is a direction a filter may travel; each `1`/`*` pair tells you which table is the describing one and which the measuring one. If every arrow points from dimensions into a single fact table, the model behaves predictably. If arrows point both ways, or if two fact tables are connected other than through shared dimensions, you have a model that can return different numbers depending on which path the engine happens to take — and that is where "why is this figure wrong?" questions come from. ## What interviewers listen for A good answer separates the two properties instead of blurring them into "the join", gives the default (one-to-many, single, from the one side to the many side), and names the consequence with an example — the slicer that fails to filter the dimension. Mentioning that only one active path may exist, and that many-to-many cardinality yields a weaker relationship, is what marks a candidate who has actually debugged a model.

  • Why does Power BI refuse to create a relationship that completes a loop between tables?
    Because a loop gives a filter more than one route to the same table, and the two routes can produce different results. The engine guarantees a single unambiguous active path, so it blocks the relationship and offers to create it as inactive instead. You resolve the ambiguity in the model — usually with a bridge or a shared dimension — rather than leaving it to chance.
  • What is different about a many-to-many cardinality relationship compared with one-to-many?
    It is a limited (weak) relationship. The engine does not add a blank row for unmatched keys, RELATED cannot traverse it from row context, and unmatched values disappear from cross-filtered results, so totals can stop matching the visible rows. It is a valid tool for relating two facts at a shared grain, but a bridge dimension with two one-to-many relationships is usually safer.
  • How do you filter a dimension by a fact table without setting the relationship direction to Both?
    Scope the propagation to a single measure with CROSSFILTER inside CALCULATE, for example counting product rows with the Sales-to-Product relationship temporarily set to BOTH. The model keeps its single-direction default for everything else, so you get the one answer you wanted without changing how every other visual in the report behaves.

saying these in an interview costs you the question

  • Describes a relationship as just a SQL join with no direction
  • Thinks filters always flow both ways by default
  • Cannot say which side of a star schema is the one side
  • Confuses cross-filter direction with the DAX filter context of a measure
  • Believes several active relationships can exist between two tables

context

resolving links…