Many tables have more than one date: an order has an Order Date, a Ship Date and a Delivery Date. Power BI allows only one active relationship between two tables, so when you connect all three dates to your Date table, two of the relationships become dotted (inactive) lines. To use them, you need the DAX function USERELATIONSHIP.
This guide explains active and inactive relationships, shows how to write measures with USERELATIONSHIP, and covers the alternative (role-playing date tables) and the common errors.
Quick answer: Create the extra relationship (for exampleSales[ShipDate]→'Date'[Date]); Power BI makes it inactive. Then writeSales by Ship Date = CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) ). The measure uses the ship-date relationship instead of the active order-date relationship, only inside that calculation.
Active vs inactive relationships
- Active relationship (solid line): used automatically by every visual and measure.
- Inactive relationship (dotted line): exists in the model but does nothing until a measure switches it on with USERELATIONSHIP.
Power BI allows only one active path between two tables to avoid ambiguity: if both Order Date and Ship Date were active, a filter on October would not know which date to use.
Example model
Sales table: OrderID, OrderDate, ShipDate, Amount. Date table: one row per day, marked as a date table (see how to create a date table).
- In Model view, drag
Sales[OrderDate]to'Date'[Date]. This becomes the active relationship. - Drag
Sales[ShipDate]to'Date'[Date]. Power BI creates it as inactive (dotted line).
To check or change which one is active: double-click the relationship line and look at Make this relationship active, or use Manage relationships.
USERELATIONSHIP syntax
USERELATIONSHIP ( <column1>, <column2> )
- The two columns are the two ends of an existing relationship (the order does not matter).
- It works only as a filter argument inside CALCULATE (or functions that take filter arguments, such as CALCULATETABLE and TOTALYTD).
- While the measure runs, the inactive relationship becomes active and the normal one is ignored.
Example 1: Sales by order date vs ship date
Total Sales = SUM ( Sales[Amount] )
Sales by Ship Date =
CALCULATE (
[Total Sales],
USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)
Put both measures in a table by month. Total Sales shows what was ordered each month; Sales by Ship Date shows what was shipped. The difference is your shipping backlog.
Example 2: Count orders shipped
Orders Shipped =
CALCULATE (
COUNTROWS ( Sales ),
USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)
Example 3: Time intelligence on the inactive date
Shipped Sales YTD =
CALCULATE (
[Total Sales],
USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ),
DATESYTD ( 'Date'[Date] )
)
Combine USERELATIONSHIP with any time-intelligence filter in the same CALCULATE.
Example 4: HR headcount (hire and exit dates)
An Employees table has HireDate and ExitDate, both related to the Date table as inactive relationships. Then:
Hires =
CALCULATE ( COUNTROWS ( Employees ), USERELATIONSHIP ( Employees[HireDate], 'Date'[Date] ) )
Leavers =
CALCULATE ( COUNTROWS ( Employees ), USERELATIONSHIP ( Employees[ExitDate], 'Date'[Date] ) )
See the full HR model in Power BI HR analytics dashboard.
Alternative: role-playing date tables
Instead of one Date table with inactive relationships, you can create separate date tables: Order Date and Ship Date, each with an active relationship.
| USERELATIONSHIP | Role-playing date tables | |
|---|---|---|
| Date tables | One | One per date role |
| Extra measures | Yes, one per date role | No, existing measures work |
| Slicers | One date slicer for everything | Separate slicers for order date and ship date |
| Best for | A few measures on a second date | Users who want to filter by both dates freely |
A quick way to create a second date table: Modeling → New table → Ship Date = 'Date', then relate it to Sales[ShipDate] and mark it as a date table.
Related function: CROSSFILTER
USERELATIONSHIP chooses which relationship to use. CROSSFILTER changes the filter direction of a relationship inside a measure (one-way, both, or none). For example, CALCULATE ( DISTINCTCOUNT ( Sales[CustomerID] ), CROSSFILTER ( Sales[ProductKey], Product[ProductKey], BOTH ) ). Use it sparingly; bi-directional filters can slow models and cause ambiguity.
Common errors and fixes
"USERELATIONSHIP function can only use the two columns references participating in relationship"
The relationship does not exist. Create it in Model view first (it can stay inactive).
The measure returns the same values as Total Sales
Check that you referenced the right columns, and that the visual uses the Date table (not Sales[OrderDate]) on its axis.
It works in a table but not with my slicer
The slicer must use a column from the Date table. A slicer on Sales[OrderDate] filters Sales directly and bypasses the relationships.
Row-level security limitation
Microsoft's documentation notes that USERELATIONSHIP cannot be used when row-level security is defined for the table in which the measure is included. Test carefully if you use RLS.
Frequently asked questions
Can I have two active relationships between the same tables?
No. Only one can be active. Use USERELATIONSHIP or a second date table for the others.
Can I use USERELATIONSHIP outside CALCULATE?
No. It is a filter modifier and must be an argument of CALCULATE or a similar function.
Does USERELATIONSHIP slow down reports?
No more than any other CALCULATE filter. It is the standard, efficient way to work with multiple dates.
Practice task
Add OrderDate and ShipDate relationships from Sales to Date. Create Total Sales and Sales by Ship Date, show both by month in a line chart, and add a measure for the difference (the backlog).
Related: CALCULATE in DAX: Complete Guide and Power BI data modeling best practices.
