Forum Discussion

KP-PowerBI's avatar
KP-PowerBI
Frequent Visitor
7 years ago
Solved

Userelationship does not return correct value in measure

Hi I have the below (simplified)   Weighting Table uID Weighting   Data Table uID_ALL uID_ALL_Aged Weight (taken from weighting table using RELATED function)   I have two relationships bet...
  • vanessafvg's avatar
    vanessafvg
    7 years ago

    looks like the related function with use relationships might not actually work

     

    see this article.    the suggestion is to use lookupvalue instead.

    https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

    Applying USERELATIONSHIP to RELATED

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

     

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

     

    But what if the user wants to obtain the day name of week of the due date? They cannot use CALCULATE and RELATEDtogether, so they have to use this syntax instead:

     

    1
    2
    3
    4
    5
    6
    7
    8
    9
    FactInternetSales[DayDue] =
    CALCULATE (
        CALCULATE (
            VALUES ( DimDate[EnglishDayNameOfWeek] ),
            FactInternetSales
        ),
        USERELATIONSHIP ( DimDate[DateKey], FactInternetSales[DueDateKey] ),
        ALL ( DimDate )
    )
     

     

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

    ResultRelated

    Though this syntax works, it is strongly advised not to use it. Indeed, it is hard to understand and it is easy to write incorrect DAX code here. A better approach is using LOOKUPVALUE instead, which does not require the relationship at all.

     

    1
    2
    3
    4
    5
    6
    FactInternetSales[DayDue] =
    LOOKUPVALUE (
        DimDate[EnglishDayNameOfWeek],
        DimDate[DateKey],
        FactInternetSales[DueDateKey]
    )