Articles   \  

USERELATIONSHIP in Calculated Columns



In a Power Pivot or Tabular model that has inactive relationships, you can use the USERELATIONSHIP function to apply an inactive relationship to a particular DAX expression. Its usage is simple in a measure, but you might consider alternative syntax in a calculated columns, as it is explained in this article.

USERELATIONSHIP in a Measure

Consider the classical AdventureWorks data mart example: the FactInternetSales table has three relationships with DimDate: one is active (using OrderDateKey) and the other two are inactive (DueDateKey and ShipDateKey).

MultipleRelationships

You can calculate the SalesByOrderDate in this way:

SalesByOrderDate := SUM ( FactInternetSales[SalesAmount] )

If you want to use one of the inactive relationships, you just have to apply the USERELATIONSHIP function in one of the CALCULATE filter arguments:

SalesByDueDate := 
CALCULATE (
    SUM ( FactInternetSales[SalesAmount] ),
    USERELATIONSHIP ( 
        FactInternetSales[DueDateKey],
        DimDate[DateKey]
    )
)

SalesByShipDate := 
CALCULATE (
    SUM ( FactInternetSales[SalesAmount] ),
    USERELATIONSHIP ( 
        FactInternetSales[ShipDateKey],
        DimDate[DateKey]
    )
)

You can see the different values for the three measures for each year in the following screenshot.

PivotWithMeasures

USERELATIONSHIP in a Calculated Column

When you define a calculated column, you are writing a DAX expression that will be executed in a row context. Since USERELATIONSHIP requires a CALCULATE to be used and the CALCULATE applies a context transition when executed within a row context, obtaining the expected behavior is not easy.

Apply USERELATIONSHIP to RELATED

If you create a calculated column in FactInternetSales, you might want to use RELATED choosing the relationship to use. Unfortunately, this is not possible. For example, if you want to denormalize the day name of week of the order date, you write:

FactInternetSales[DayOrder] =
RELATED ( DimDate[EnglishDayNameOfWeek] )

But what if you want to obtain the day name of week of the due date? You cannot use CALCULATE and RELATED together, so you have to use this syntax instead:

FactInternetSales[DayDue] =
CALCULATE ( 
    CALCULATE ( 
        VALUES ( DimDate[EnglishDayNameOfWeek] ), 
        FactInternetSales 
    ), 
    USERELATIONSHIP ( DimDate[DateKey], FactInternetSales[DueDateKey] ), 
    ALL ( DimDate ) 
)

There are 2 CALCULATE required in this case: the outermost CALCULATE applies the USERELATIONSHIP to the innermost CALCULATE, and the ALL ( DimDate ) filter remove the existing filter that would be generated by the context transition. The innermost CALCULATE applies the FactInternetSales to the filter condition and thanks to the active USERELATIONSHIP, its filter propagates to the lookup DimDate table using the DueDateKey relationship instead of the OrderDateKey one. The result is visible in the following screenshot.

ResultRelated

Even if this syntax works, I strongly discourage you using it, because it is hard to understand and it is easy to write wrong DAX code here. A better approach is using LOOKUPVALUE instead, which does not require the relationship at all.

FactInternetSales[DayDue] =
LOOKUPVALUE ( 
    DimDate[EnglishDayNameOfWeek], 
    DimDate[DateKey], 
    FactInternetSales[DueDateKey] 
)

I do not have performance measures, but in a calculated column the difference is not important and I would favor the clarity of the expression in any case.

Apply USERELATIONSHIP to RELATEDTABLE

If you use RELATEDTABLE in a calculated column, you cannot apply USERELATIONSHIP directly, but you can easily do that by replacing RELATEDTABLE with CALCULATETABLE. Consider the following calculated column in DimDate table:

DimDate[OrderSales] = 
SUMX ( 
    RELATEDTABLE( FactInternetSales ), 
    FactInternetSales[SalesAmount]
)

You can always rewrite RELATEDTABLE by using CALCULATETABLE.

DimDate[OrderSales] = 
SUMX ( 
    CALCULATETABLE( FactInternetSales ), 
    FactInternetSales[SalesAmount]
)

These are in reality the same function, but you can apply additional filter arguments to CALCULATETABLE. Thus, you can write a DueSales calculated column in this way:

DimDate[DueSales] =
SUMX ( 
    CALCULATETABLE ( 
        FactInternetSales,
        USERELATIONSHIP ( 
            FactInternetSales[DueDateKey], 
            DimDate[DateKey] 
        )
    ), 
    FactInternetSales[SalesAmount] 
)

You can see in the following screenshot the result of the two calculated columns:

ResultRelatedTable

Thus, using USERELATIONSHIP is possible if you use CALCULATETABLE instead of RELATEDTABLE.







 
Want to read more?