You can use the USERELATIONSHIP function in DAX to apply a non-active relationship in a particular DAX calculation. This approach is usually simple in a measure (just use USERELATIOSHIP in one of the filter arguments of CALCULATE) but as soon as you try using it in a calculated column, you can find several issues.

In the article USERELATIONSHIP in Calculated Columns on SQLBI web site I described how to correctly write USERELATIONSHIP in this scenario, considering both sides of the relationship. AS you will see, using LOOKUPVALUE instead of USERELATIONSHIP can simplify your DAX code when you access to a lookup table!

CALCULATE modifier

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> )

Context transition

Evaluates an expression in a context modified by filters.

CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )


Retrieves a value from a table.

LOOKUPVALUE ( <Result_ColumnName>, <Search_ColumnName>, <Search_Value> [, <Search_ColumnName>, <Search_Value> [, … ] ] [, <Alternate_Result>] )