DAX USERELATIONSHIP function
Uses an inactive relationship for one calculation.
A table can have only one active relationship to the date table. USERELATIONSHIP switches to another one (like Ship Date instead of Order Date) inside a CALCULATE.
Syntax
USERELATIONSHIP(<column1>, <column2>)| Argument | Required | Description |
|---|---|---|
| column1 | Required | One end of an existing (inactive) relationship, usually the many side. |
| column2 | Required | The other end, usually the date table's date column. |
Returns: Nothing: it changes how the CALCULATE around it filters.
Examples
Calculated on the free practice data (table financials), with [Total Sales] = SUM(financials[Sales]) and [Total Profit] = SUM(financials[Profit]).
1. Sales by ship date, by year
Sales by Ship Date = CALCULATE([Total Sales], USERELATIONSHIP(financials[Ship Date], Dates[Date]))| Year | Sales by Ship Date |
|---|---|
| 2013 | 25,275,493.21 |
| 2014 | 92,354,666.26 |
| Total | 118,726,350.26 |
The same sales, dated by when they shipped. 2013 and 2014 add up to less than the total: some December 2014 orders shipped in January 2015, outside the date table. The total row has no date filter, so it still counts them.
Good to know
- The relationship must already exist in the model, set as inactive (dashed line).
Related functions
Official reference: USERELATIONSHIP function on Microsoft Learn