Downloads
DAXRelationships and lookupIntermediate

USERELATIONSHIP

Activates an inactive relationship for one calculation, inside CALCULATE.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals