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
timalbers's avatar
timalbers
Skilled Sharer
3 years ago

Thank you very much PabloDeheza !

This is actually something I wasn't aware of. 

 

We are almost there, there still remains a problem with the following part:

Kunden[Gruppe] = "Intercompany" && Sales[Mandant] = "50"

 

I am not able to use two fields of two different tables for a logical operation without FILTER & RELATED. Or at least I don't know how to..

 

When I use AND instead of && it also throws an error.

 

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!