Launch Deal · 50% off 1st month ·

3.2 — Star Schema and Relationships in a Semantic Model

DP-600 Domain 3 · Skill 3.2

What You Need to Know

Power BI models are built for star schemas. Relationships carry filters from dimensions to facts, and most modeling questions are really about cardinality, filter direction and many-to-many cases.

Relationship properties

Property Options Guidance
Cardinality One-to-many (1:*), one-to-one (1:1), many-to-many (*:*) Dimension (one side) to fact (many side) is the norm
Cross-filter direction Single, Both Use Single, from dimension to fact. Use Both only where it's needed, such as a bridge, because it can create ambiguity and slow queries
Active / inactive One active path between two tables Use inactive relationships for role-playing dates, turned on in a measure with USERELATIONSHIP
Assume referential integrity DirectQuery only Lets the engine use inner joins, which is faster, when the keys always match

Common patterns

  • Role-playing dimensions: one Date table, several date keys in the fact (OrderDate, ShipDate). Keep one relationship active and the others inactive, and use CALCULATE([Sales], USERELATIONSHIP(Fact[ShipDateKey], 'Date'[DateKey])).
  • Bridge tables (many-to-many between dimensions): Customer → Bridge ← Account → Fact. Set the bridge-to-account relationship to Both (or use CROSSFILTER in measures) so customer filters reach the fact. Expect totals that count shared items more than once.
  • Many-to-many cardinality relationships (*:*): a direct relationship between two tables that both have duplicate key values, for example a budget at Category level against a Product dimension. Filters work, but understand the grain mismatch.
  • Different grains: budgets by month and category, sales by day and product. Relate the budget to dimension tables at its own grain (a Category table, a Month table), or use a *:* relationship carefully.
  • Snowflakes: flatten them where you can (product, subcategory and category in one table). It means fewer relationships and simpler filtering.

Model hygiene

  • Hide foreign keys and technical columns, and expose measures instead of letting users drag raw numeric columns.
  • Mark the Date table (Mark as date table) so time intelligence works, and turn off Auto date/time in larger models.
  • Set Sort by column (month name by month number), data categories, formats, and display folders.

Key Concept: Filters flow one way, from dimension to fact, through 1:* relationships. Use inactive relationships plus USERELATIONSHIP for role-playing dates, and a bridge table (with deliberate bidirectional filtering) for many-to-many.

Exam Traps

  • Making every relationship bidirectional "to be safe" (it causes ambiguity and slows queries)
  • Creating three copies of the Date table instead of one table with inactive relationships and USERELATIONSHIP
  • Relating a monthly budget to daily sales on a date key that doesn't exist at that grain
  • Leaving keys visible so that users sum ID columns

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 17 DP-600 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.