Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
timalbers
Advocate V
Advocate V

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 table which contains sales information
  • There are two different date columns in the sales table, let's call them order date and invoice date
  • I have a date dimension table with my main date hierarchy
  • There is an active relationship from the date table to the order date column of my fact table
  • There is an inactive relationship from the date table to the invoice date column of my fact table
  • I want to create a matrix visual with the following projections:
    • Rows: Some category fields, like product group, manufacturer, etc.
    • Columns: Years of my date table
    • Values: A specific measure which counts elements in the sales table
  • The main problem is that in this matrix I want to use the year of the invoice date as columns, which has not an active relationship to the date table - this is not supposed to be changed since the order date relationship should remain the main connection

The measure I am talking about looks something like this:

 

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

 

The expression could also be a sum or any other aggregation.

 

So, I thought I know very well how to easily solve this issue by adding the USERELATIONSHIP function as an filter argument of my CALCULATE function inside the measure. However, something like this will not work:

 

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

 

The values in my matrix are still segemented into the year columns by the order date.

 

Next I tried to outsource the measure calculation into a variable. But like before, this one won't work either:

 

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

 

 

The last thing I've tried was the key to success. Instead of only evaluating the calculation once inside a single measure, I created a second measure additionally to the base measure (which is the one from the first code block). The second measure simply refers to the base measure and applies the different relationship behaviour:

 

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

 

 

Now I know how to handle such cases, everything works fine for me. But this issue still triggers me, because I am missing the point, what exactly is the reason for this behaviour. Personally as a modeller I would prefer to only have one measure, as there is only one output I am going to use for my data model. The base measure will not be used in any other place. It's just a pre-calculation.

 

Hopefully anyone can explain the background of this behaviour and maybe even provide a solution where I am able to combine the evaluation steps into one single measure.

 

Thanks a lot if you read this far!

 

Cheers from Germany

6 REPLIES 6
PabloDeheza
Solution Sage
Solution Sage

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!

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.

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/

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.

 

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
    )

 

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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.