Relationships & Advanced Modeling

Relationships in semantic models

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

1. Products → ProductPromotions (one-to-many on product_key)
2. Promotions → ProductPromotions (one-to-many on promotion_key)
3. 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

Question

What is a bridge table?

Click or press Enter to reveal answer

Answer

A bridge table resolves a many-to-many relationship by sitting between two dimension tables. It contains one row per combination (e.g., product_key + promotion_key). Each dimension has a one-to-many relationship to the bridge table, with bidirectional filtering to propagate filters through.

Click to flip back

Question

What is a role-playing dimension?

Click or press Enter to reveal answer

Answer

A dimension table that participates in multiple relationships with the same fact table. Example: dim_date used as order date, ship date, and delivery date. Only one relationship can be active — others are inactive and activated with USERELATIONSHIP in DAX.

Click to flip back

Question

What does USERELATIONSHIP do?

Click or press Enter to reveal answer

Answer

USERELATIONSHIP activates an inactive relationship within a CALCULATE expression. Syntax: CALCULATE([Measure], USERELATIONSHIP(FK_column, PK_column)). Required when a role-playing dimension has multiple relationships and you need to use a non-default one.

Click to flip back

Knowledge Check

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?

ACreate a second copy of dim_date called dim_ship_date and create a new active relationshipBUse USERELATIONSHIP inside CALCULATE to activate the inactive ship_date relationshipCChange the active relationship from order_date to ship_dateDUse FILTER(dim_date, dim_date[date_key] = fact_orders[ship_date_key])

Check Answer

Knowledge Check

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?

ADirect many-to-many relationship between dim_patient and dim_care_teamBA bridge table (patient_care_team) with one-to-many relationships from each dimension and bidirectional filteringCMerge the care team attributes directly into the patient dimension tableDCreate a separate semantic model for each care team

Check Answer

🎬 Video coming soon

---

Next up: DAX Essentials: Variables & Functions — master DAX iterators, table filtering, windowing, and information functions.