Launch Deal · 50% off 1st month ·

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.

See plans

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.