Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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
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!
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |