Relationships & Advanced Modeling
Bridge tables, many-to-many relationships, role-playing dimensions — master the relationship patterns that the exam loves to test.
Relationships in semantic models
Think of relationships as bridges between islands.
Each table in your semantic model is an island. To get data from one island (Sales) to another (Products), you need a bridge (relationship). The bridge has a direction (one-way or two-way), a width (one-to-many or many-to-many), and rules about who can cross (filter direction).
In a star schema, the fact table is the central island with bridges to each dimension. Advanced patterns add extra bridges for special cases — bridge tables, role-playing dimensions, and many-to-many relationships.
Relationship properties
Every relationship has four properties:
| Property | Options | Default |
|---|---|---|
| Cardinality | One-to-many, Many-to-one, One-to-one, Many-to-many | One-to-many |
| Cross-filter direction | Single, Both | Single |
| Active | Yes / No | Yes (only one active per pair) |
| Assume referential integrity | Yes / No | No |
Cross-filter direction
- Single (default) — filters flow from the “one” side to the “many” side. Dimension filters the fact table.
- Both (bidirectional) — filters flow both ways. Use cautiously — can cause ambiguity and performance issues.
Exam tip: Bidirectional filtering dangers
The exam loves to test when bidirectional filtering goes wrong:
- Ambiguous paths — if table A can filter table C through two different bidirectional paths, the engine cannot determine which path to use
- Performance — bidirectional filters create more complex query plans
- Unexpected results — a filter on a fact table can propagate back to filter dimensions, giving wrong counts
Rule of thumb: use single direction (dimension → fact) unless you have a specific, justified reason for bidirectional.
Many-to-many relationships
When two tables have a many-to-many relationship (e.g., students and courses, products and promotions), you need a bridge table.
The bridge table pattern
Products ←→ ProductPromotions ←→ Promotions
(many) (bridge table) (many)The bridge table contains one row for each combination:
| product_key | promotion_key |
|---|---|
| 101 | 501 |
| 101 | 502 |
| 102 | 501 |
| 103 | 503 |
Relationship setup
Products→ProductPromotions(one-to-many on product_key)Promotions→ProductPromotions(one-to-many on promotion_key)- Bidirectional on both relationships (so filters propagate through the bridge)
Scenario: Anita's promotional analysis
Anita at FreshCart has a many-to-many relationship between products and promotions — one product can be in multiple promotions, and one promotion covers multiple products.
She creates a bridge_product_promotion table and sets up two one-to-many relationships:
dim_product→bridge_product_promotion(filter from product to bridge)dim_promotion→bridge_product_promotion(filter from promotion to bridge)
With bidirectional filtering enabled, selecting “Easter Sale” in a report filter shows only the products in that promotion, and selecting “Organic Avocado” shows only the promotions it participated in.
Role-playing dimensions
A role-playing dimension is a single dimension table used in multiple relationships with the same fact table. The most common example: a date dimension.
Example: Order date vs ship date vs delivery date
A fact table with three date columns needs three relationships to the date dimension:
| Relationship | Fact Column | Dim Column | Active? |
|---|---|---|---|
| Order Date | fact_orders.order_date_key | dim_date.date_key | Active |
| Ship Date | fact_orders.ship_date_key | dim_date.date_key | Inactive |
| Delivery Date | fact_orders.delivery_date_key | dim_date.date_key | Inactive |
Only ONE relationship between two tables can be active. The others are inactive and must be activated explicitly in DAX using USERELATIONSHIP:
ShipDateRevenue =
CALCULATE(
[Total Revenue],
USERELATIONSHIP(fact_orders[ship_date_key], dim_date[date_key])
)Exam tip: USERELATIONSHIP
The exam frequently tests USERELATIONSHIP. Remember:
- Only one relationship per table pair can be active at a time
- USERELATIONSHIP activates an inactive relationship within a CALCULATE context
- The function takes the foreign key column and the primary key column as arguments
- It only works inside CALCULATE (not in regular expressions)
Star schema in semantic models vs physical storage
In Module 8 (Star Schema Design), you built the physical star schema in a lakehouse or warehouse. Here, you implement the same pattern as relationships in the semantic model.
| Aspect | Physical (Lakehouse/Warehouse) | Semantic Model |
|---|---|---|
| Star shape | Fact + dimension tables stored as Delta/SQL | Tables connected by relationships |
| Joins | SQL JOIN clauses | Automatic via relationships |
| Denormalization | Flatten hierarchies in SQL | Flatten or handle with hierarchies |
| Performance | Columnar storage, aggregate tables | VertiPaq compression, relationship optimization |
| Business logic | SQL views and stored procedures | DAX measures and calculation groups |
Anita at FreshCart has a fact_orders table with order_date_key, ship_date_key, and delivery_date_key — all referencing dim_date. Only the order_date relationship is active. She needs a measure for revenue by ship date. What should she do?
Dr. Sarah at Pacific Health has patients who can be assigned to multiple care teams, and each care team has multiple patients. What relationship pattern should she use in her semantic model?
Next up: DAX Essentials: Variables & Functions — master DAX iterators, table filtering, windowing, and information functions.