Forum Discussion

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

Thank you very much for your super fast answer PabloDeheza, I appreciate it!

You have a point there, the FILTER statements are not needed in my example.

Problem is, my actual measure is a little bit more complex, I just tried to keep it simple for explanation.

 

My measure looks something like this:

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

As you can see, there are many filters that are coming from other dimension tables via RELATED.

I cannot use those filters without a FILTER function, because RELATED needs the row context.

 

So this is the reason I wrapped every argument inside a FILTER statement.
I could remove it for the filters in the fact table, but not for those that come from a related table.

PabloDeheza's avatar
PabloDeheza
Icon for Solution Sage rankSolution Sage
3 years ago

If those filters with related are coming from other dimensions and the relationship between Sales and the Dimension is Many (Sales) to One (Dimension) then you shouldnt need RELATED. As you mention RELATED needs a row context but you could just simply apply a filter on the dimension, something like this:

CALCULATE(
    DISTINCTCOUNTNOBLANK( InternetSales[ProductKey] ),
    InternetSales[StoreKey] = 306 ,
    InternetSales[Net Price] > 8 ,
    'Product'[Brand] = "Contoso",
    OR(
        'Product'[Color] = "Blue",
        'Product'[Color] = "Red"
    ),
    USERELATIONSHIP( InternetSales[Delivery Date], TablaFecha[Fecha] )
)

 

In your case if im not mistaken it would be:

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

 

Again, this will work if best practices of data modeling are followed, that is mainly implementing a Star Schema
https://www.sqlbi.com/articles/power-bi-star-schema-or-single-table/

  • timalbers's avatar
    timalbers
    Icon for Skilled Sharer rankSkilled 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
      Icon for Solution Sage rankSolution 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
        Icon for Skilled Sharer rankSkilled 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!