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
CROSSFILTERin 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.
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
- Understand star schema and the importance for Power BI, Microsoft Learn
- Model relationships in Power BI Desktop, Microsoft Learn
- Many-to-many relationship guidance, Microsoft Learn
- Active vs inactive relationship guidance, Microsoft Learn