Forum Discussion

timalbers's avatar
timalbers
Skilled Sharer
3 years ago

CALCULATE + USERELATIONSHIP: Evaluation behaves differently when split into to measures

Hi all, today I have a very theoretical question for you, which primarily aims to get a better understanding of how exactly DAX behaves in this scenario and why it does.   Given: I have a fact t...
Need Help
PabloDeheza's avatar
PabloDeheza
Solution Sage
3 years ago

The only idea that comes to my mind at the moment is using an IF statement, where:

VAR _Intercompany = 
    CALCULATE(
        DISTINCTCOUNTNOBLANK( Sales[Item ID] ),
        Sales[Vertriebsart] = "Handel" ,
        Sales[Typ] = "N" ,
        Produktgruppe[Key] = "M",
        Sales[Mandant] = "50",
        Sales[Rechnungsnummer] <> BLANK() ,
        'AP'[P_Text]  <> "Dublette" 
    )
VAR _NotIntercompany = 
    CALCULATE(
        DISTINCTCOUNTNOBLANK( Sales[Item ID] ),
        Sales[Vertriebsart] = "Handel" ,
        Sales[Typ] = "N" ,
        Produktgruppe[Key] = "M",
        Kunden[Gruppe] <> "Intercompany", 
        Kunden[Gruppe] = "Intercompany" && Sales[Mandant] = "50"
        ),
        Sales[Rechnungsnummer] <> BLANK(),
        'AP'[P_Text]  <> "Dublette" 
    )
RETURN
    IF(
        Kunden[Gruppe] = "Intercompany",
        _Intercompany,
        _NotIntercompany
    )

 

timalbers's avatar
timalbers
Skilled Sharer
3 years ago

Thanks PabloDeheza !

 

Although I was not able to implement your solution correctly into my model, your idea led me to one that works for me.

 

Instead of two options which are chosen based on an IF condition I created two tables with the different filter settings in two variables and merged them together. After that the measure counts the distinct rows, which outputs exactly the number I wanted.

Here's my solution:

VAR _Intercompany = 
    CALCULATETABLE(
        DISTINCT( Sales[Item ID] ),
        .
        .    // common filters
        .
        Kunden[Gruppe] = "Intercompany",
        Sales[Mandant] = "50",
        USERELATIONSHIP( Sales[Rechnungsdatum], Date[Date] )
    )

VAR _NotIntercompany = 
    CALCULATETABLE(
        DISTINCT( Sales[Item ID] ),
        .
        .    // common filters
        .
        Kunden[Gruppe] <> "Intercompany", 
        USERELATIONSHIP( Sales[Rechnungsdatum], Date[Date] )
    )

VAR _combine =
    DISTINCT( 
        UNION( _Intercompany , _NotIntercompany )
    )

RETURN
    COUNTROWS( _combine )

 

Thank you very much for your help!