USERELATIONSHIP
Activates an inactive relationship for one calculation, inside CALCULATE.
USERELATIONSHIP(<column1>, <column2>)Returns: Used inside CALCULATE or CALCULATETABLE. It is not a value by itself.
What it does
A model can hold one active relationship between two tables and inactive relationships for other purposes. A typical sales model has an active link from the sales date to the date table (order date), and an inactive link from a ship date column. USERELATIONSHIP switches the inactive link on for a single calculation. A measure can then report sales by ship date next to sales by order date.
Syntax and arguments
- column1: the column on the many side of the inactive relationship, such as
Sales[ShipDate]. - column2: the column on the one side, such as
'Date'[Date]. - Both columns must already be related in the model, even though the relationship is inactive.
Example
Sales by ship date. This assumes the model has a ShipDate column on Sales, with an inactive relationship to the date table. The sample Contoso columns do not include it, so treat this as the pattern.
Sales by Ship Date =
CALCULATE (
[Total Sales],
USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)
The measure groups the sales by the ship date, while the active order-date relationship is ignored for this calculation only. The model itself does not change.
Example: a second use
The timing difference between order and shipment for a month. This is the kind of line a controller reviews at a cutoff.
Shipping Timing Difference =
[Sales by Ship Date] - [Total Sales]
The result is the amount that was ordered in one period and shipped in another. A positive number means more was shipped than ordered in the period, and a negative number means the reverse. Both measures must be filtered by the same dates for this to make sense.
Things to watch
- Use it only inside CALCULATE or CALCULATETABLE, as a filter argument.
- The relationship must exist in the model. If it does not, the formula returns an error.
- The date table should be marked as a date table, and its dates must be unique.
- Many visuals follow the active relationship. Expect differences between the two views and explain them.
- It does not change the model. The relationship stays inactive for every other measure.
In business terms
Revenue recognition uses the order or invoice date, logistics uses ship dates, and receivables use due dates. Finance teams often need two dates on one page to reconcile timing differences between periods.