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
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!