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

Hey timalbers !
I believe that FILTER functions are messing up your code. You should try:

Measure = 
CALCULATE(
    DISTINCTCOUNTNOBLANK( Sales[Item ID] ),
    Sales[Product Group] = "abc" ,
    Sales[Price] > 500 ,
    ...
    USERELATIONSHIP( Sales[Invoice Date], Date[Date] )
)

FILTER inside CALCULATE are most of the times not needed. Theoretically calculate applies a FILTER by default, so 

CALCULATE (
    <expression>,
    table[column] = <value> 
)

is internally transformed into

CALCULATE (
    <expression>,
    FILTER (
        ALL ( table[column] ),
        table[column] = <value> 
    )
)

 

Here is some documentation on the subject the will explain it better than me:
https://www.sqlbi.com/articles/filter-arguments-in-calculate/

https://www.sqlbi.com/articles/specifying-multiple-filter-conditions-in-calculate/

 

Hope this helps!

  • timalbers's avatar
    timalbers
    Skilled 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
      Solution 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
        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.