PL-300: how to implement inactive relationships and USERELATIONSHIP in DAX
In this guide I teach the skill: implement and use inactive relationships between tables and activate them in DAX measures with USERELATIONSHIP. It is a common technique in Power BI models to handle multiple dates (e.g.: order date vs. ship date) and is useful both for the PL-300 exam and for accurate business reports.
What you need to know
In a Power BI model, a relationship between two tables can be active or inactive. The active relationship is what the Tabular engine uses by default to propagate filters. Inactive relationships exist in the model but do not influence filters unless they are explicitly activated by DAX. This is essential when a fact table has multiple date columns and you should use only one of them as the main relationship with the date table.
Example: you have a Sales table with columns OrderDate and ShipDate, and a DateCalendar table with Date. Normally you create an active relationship between DateCalendar[Date] and Sales[OrderDate]. To analyze by ShipDate, you create a second relationship between DateCalendar[Date] and Sales[ShipDate] and set it as inactive. To use the inactive relationship in a measure, you use USERELATIONSHIP inside a CALCULATE expression.
In practice: how it works
Essential steps to implement and use an inactive relationship with USERELATIONSHIP:
Model the relationships in Power BI Desktop: create two relationships between DateCalendar and Sales (one active for OrderDate, the other inactive for ShipDate).
Create DAX measures that activate the desired relationship only when necessary, using CALCULATE + USERELATIONSHIP.
Use the measures in visuals; filter propagation will respect the relationship activated inside the measure without changing the global active relationship.
Example of a DAX measure for sum of sales by ship date:
SalesAmount_ByShipDate =
CALCULATE(
SUM(Sales[SalesAmount]),
USERELATIONSHIP(DateCalendar[Date], Sales[ShipDate])
)
How this works: CALCULATE changes the evaluation context and USERELATIONSHIP specifies which relationship between two columns should be used temporarily. Outside this measure, the active relationship (OrderDate) remains the default.
When to use vs. alternatives
Use inactive relationships + USERELATIONSHIP when:
- There are multiple date columns in a fact table and you want to analyze by each one without duplicating the date table.
- You need specific measures that consider different relationships between tables.
Alternatives: create multiple copies of the date table (Calendar for OrderDate, Calendar for ShipDate) and relate them actively; this approach can simplify some analyses but increases the number of tables and model complexity.
Common mistakes
- Assuming USERELATIONSHIP changes the global relationship — USERELATIONSHIP only activates the relationship in the context of the measure where it is used; it does not change the model permanently.
- Using USERELATIONSHIP outside of CALCULATE — USERELATIONSHIP needs to be used inside a function that changes context (such as CALCULATE); placing it alone will cause an error.
- Confusing filter direction — even with USERELATIONSHIP, the filter direction of the relationship defined in the model (single / both) still matters; check it when modeling.
How to practice
Practical training method:
- Create a simple model: DateCalendar table (Date, Year, Month) and Sales (OrderID, OrderDate, ShipDate, SalesAmount).
- Define the active relationship between DateCalendar[Date] and Sales[OrderDate] and create an inactive relationship between DateCalendar[Date] and Sales[ShipDate].
- Implement measures:
SalesAmount_ByOrderDate = SUM(Sales[SalesAmount])
SalesAmount_ByShipDate =
CALCULATE(
SUM(Sales[SalesAmount]),
USERELATIONSHIP(DateCalendar[Date], Sales[ShipDate])
)
4. Place a table/line visual with DateCalendar[Year], DateCalendar[Month] and both measures to see the difference. 5. Try to create a measure that compares both (e.g.: var pct = DIVIDE([ByShipDate],[ByOrderDate])) and validate the results in scenarios where ShipDate differs from OrderDate.
For exam-oriented practice, use the official Microsoft Practice Assessment (free) and the official Study Guide (free) that cover this skill within the “Model the data” section. These resources offer assessment and references without violating exam rules.
In summary
- Inactive relationships allow keeping multiple links between tables without altering the model’s global behavior.
- USERELATIONSHIP is used inside CALCULATE to temporarily activate an inactive relationship in a DAX measure.
- Always check the filter direction and test measures with visuals to ensure results match the business expectations.
- Practice with simple models and use the official Microsoft resources (Practice Assessment and Study Guide) to prepare for PL-300.