Relationships and Cross-Filter Direction
Arjun loads Sales, Stores and Products into the model, drops City onto a chart with a Total Revenue measure, and every bar shows 600. The tables sit side by side but know nothing about each other. Relationships are the paths that let a filter on one table reach another.
The rule
Filters flow from the one side to the many side.
In Model view, drag Stores[StoreID] onto Sales[StoreID], or let Power BI detect the link. Every relationship has three settings.
Cardinality — how many rows can match on each side:
- One-to-many (
1:*): the key is unique in one table (Stores) and repeats in the other (Sales). This is the normal link from a dimension to a fact. - One-to-one (
1:1): unique on both sides. It often means the two tables should really be one. - Many-to-many (
*:*): the key repeats on both sides. It is allowed, but easy to misread; avoid it while you are learning.
Cross-filter direction — which way filters travel:
- Single: from the one side (Stores) to the many side (Sales). It is the default for one-to-many and the right choice almost every time.
- Both: filters also travel back from Sales to Stores. It helps in a few special cases, but it can create ambiguous paths and slow the model, so use it sparingly.
Active or inactive — only one active path may join two tables. Any extra relationship is drawn as a dashed line and stays inactive until a DAX formula switches it on with USERELATIONSHIP.
Leaf & Cup's model is a star: Stores 1:* Sales, Products 1:* Sales and Date 1:* Sales, all Single. Now Total Revenue by City shows Pune 240 and Mumbai 360, which add up to 600.
The shortcut
Picture the model as a hill: dimensions at the top, the fact at the bottom. Filters roll downhill. They never roll uphill on a Single relationship, so a filter cannot cross from one dimension to another through the fact.
The setup
A slicer on Stores[City] is set to Pune. Beside it, a table visual lists only Products[Product], with no measure.
Drills
Answer fast — trust the rule.
StoreID is unique in Stores and repeats in Sales. What is the cardinality from Stores to Sales?
With no relationship between Stores and Sales, what does a chart of Total Revenue by City show?
With the relationship in place, what does Total Revenue show for Mumbai?
A relationship line in Model view is dashed. What does that mean?
A City slicer sits beside a table visual that lists only Products[Product]. With Single relationships, what happens when Pune is picked?
Remember
- Filters flow from the one side to the many side.
- Picture a hill: dimensions on top, the fact below, filters rolling downhill.
- Traps: reaching for Both when a measure would do, and forgetting that dashed lines are inactive.