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
