2.2 — Relationships, Cardinality, Cross-Filter Direction and Role-Playing Dimensions
PL-300 Domain 2 · Skill 2.2
What You Need to Know
A relationship lets filters on one table reach another. You create it by dragging one column onto another in Model view, or with Manage relationships.
Cardinality
| Cardinality | Meaning | Typical case |
|---|---|---|
| One-to-many (1:*) / Many-to-one (*:1) | Unique values on the "one" side | Product (one) to Sales (many). The normal star schema |
| One-to-one (1:1) | Unique on both sides | Usually a sign the two tables should be merged |
| Many-to-many (*:*) | Duplicates on both sides | Sales targets at Category level related to a Product table at product level. Use with care |
For a true many-to-many between two dimensions (customers and accounts with joint owners), the standard pattern is a bridging table with two one-to-many relationships.
Cross-filter direction
- Single (default for one-to-many): filters flow from the "one" side to the "many" side, from dimension to fact.
- Both: filters flow both ways. Useful for a bridging table, or to make a slicer show only values that have data, but it can create ambiguous paths, slower queries and surprising totals. Prefer Single and use
CROSSFILTER()inside a measure when one calculation needs the other direction. - Apply security filter in both directions: only applies to RLS (lesson 4.6). It lets an RLS filter flow across a bidirectional relationship.
Active and inactive relationships
Only one active path can exist between two tables. A second relationship between the same tables is inactive (a dashed line).
Role-playing dimensions
A role-playing dimension is one dimension used for several roles. The classic case: Sales has OrderDate, ShipDate and DueDate, all relating to one Date table.
| Approach | How | When |
|---|---|---|
| Inactive relationships + USERELATIONSHIP | Keep OrderDate active. Make ShipDate and DueDate inactive. Write Sales by Ship Date = CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])) |
Few measures need the other roles |
| Copy the dimension per role | Create Ship Date and Due Date tables (a referenced query, or a calculated table such as Ship Date = 'Date'), each with its own active relationship |
Users must slice or filter by each role independently, or see order and ship dates in the same visual |
USERELATIONSHIP only works inside CALCULATE (or CALCULATETABLE) and only for a relationship that already exists in the model.
Weak relationships and blanks
- If the "many" side has keys that don't exist on the "one" side, visuals show a (Blank) member for those rows. Fix the data or add an "Unknown" row.
- Relationships between tables in different source groups of a composite model are limited relationships. They behave like inner joins.
Key Concept: Keep one-to-many relationships with single direction as the default. Handle role-playing dates with inactive relationships plus USERELATIONSHIP, or with a copy of the date table per role when users need to filter by each one.
Exam Traps
- Setting every relationship to Both to "make the slicer work" (creates ambiguity and slower models)
- Trying to make two relationships between the same tables active at once
- Using USERELATIONSHIP outside CALCULATE, or for a relationship that doesn't exist
- Choosing USERELATIONSHIP when users must slice by ship date and order date at the same time (copy the dimension instead)
- Ignoring a (Blank) row in a visual that signals missing dimension keys
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 22 PL-300 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Model relationships in Power BI Desktop, Microsoft Learn
- Active vs inactive relationship guidance, Microsoft Learn
- Bi-directional relationship guidance, Microsoft Learn
- Many-to-many relationship guidance, Microsoft Learn