If two tables are linked by more than one relationship, you can decide which relationship to activate by using USERELATIONSHIP. Indeed, you can only have one active relationship between any two tables. For example, you start with a model where Sales is related to Date through the Date column, like in the following picture.
If you create a second relationship, based on the Delivery Date columns, the second relationship is an inactive relationship.
Inactive relationships are – by nature – non-active. Consequently, if you filter by Year the report shows the sales whose Order Date belongs to the filtered year. Delivery Date is not used as part of the default filtering. In order to filter Sales based on Delivery Date, you can temporarily activate a relationship using USERELATIONSHIP.
USERELATIONSHIP is a CALCULATE modifier, which instructs CALCULATE to temporarily activate the relationship. When CALCULATE has computed its result, the default relationship becomes active again.
The Delivery Amount measure below computes the amount delivered within a certain time period, in contrast with the ordered amount returned by the Sales Amount measure:
Delivery Amount := CALCULATE ( [Sales Amount], USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] ) )
You can compare Delivery Amount with Sales Amount in the following figure.
The arguments of USERELATIONSHIPS are the two columns that form the relationship. The order of the columns is not relevant, even though it is common practice to use the column of the many-side of the relationship (Sales in our example) first, and the one-side (Date in the example) second. With that said, it is just a standard convention; the opposite order does not alter the result.
The relationship must already be in the model. You cannot use USERELATIONSHIP to create a relationship on the fly. USERELATIONSHIP can only activate an existing relationship.
Specifies an existing relationship to be used in the evaluation of a DAX expression. The relationship is defined by naming, as arguments, the two columns that serve as endpoints.
USERELATIONSHIP ( <ColumnName1>, <ColumnName2> )
Evaluates an expression in a context modified by filters.
CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )
Articles in the DAX 101 series
- Computing running totals in DAX
- Counting working days in DAX
- Summing values for the total
- Year-to-date filtering weekdays in DAX
- Creating a simple date table in DAX
- Automatic time intelligence in Power BI
- Using CONCATENATEX in measures
- Previous year up to a certain date
- Sorting months in fiscal calendars
- Using USERELATIONSHIP in DAX
- Mark as Date table
- Row context in DAX
- Filter context in DAX
- Introducing CALCULATE in DAX
- Understanding context transition in DAX
- Using KEEPFILTERS in DAX
- Variables in DAX
- Using RELATED and RELATEDTABLE in DAX
- Introducing ALLSELECTED in DAX
- Introducing RANKX in DAX