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.
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.
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
- Mark as Date table (Jul 20, 2020)
- Using USERELATIONSHIP in DAX (May 18, 2020)
- Sorting months in fiscal calendars (Nov 25, 2019)
- Previous year up to a certain date (Sep 12, 2019)
- Using CONCATENATEX in measures (Jun 24, 2019)
- Automatic time intelligence in Power BI (May 13, 2019)
- Creating a simple date table in DAX (May 11, 2019)
- Year-to-date filtering weekdays in DAX (Apr 29, 2019)
- Summing values for the total (Apr 15, 2019)
- Counting working days in DAX (Mar 13, 2019)
- Computing running totals in DAX (Mar 4, 2019)