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
- Using USERELATIONSHIP in DAX
- Sorting months in fiscal calendars
- Previous year up to a certain date
- Using CONCATENATEX in measures
- Automatic time intelligence in Power BI
- Creating a simple date table in DAX
- Year-to-date filtering weekdays in DAX
- Summing values for the total
- Counting working days in DAX
- Computing running totals in DAX