In this writing, I want to share how I will going to find out what will happen if I write, for instance, CALCULATE ( CALCULATE ( SUM(SALES[Quantity] ), DATESYTD ( CALENDAR[Dates] ) ) ). I did not try before, and let's find out right now.
Firstly, I will try to create a sample situation where I may need the above DAX formula.
I think, I need the above when I try to find out Year To Date quantity by customer inside VAR with using ADDCOLUMNS. It will look something like below.
On line number 6 and 7, you can see that CALCULATE ( CALCULATE (...) ) is written. In my mind, why I was thinking to write like this is because I needed Year To Date (first CALCULATE) measure with creating context transition (second CALCULATE).
Before I step further, I want to admit that I never wrote like this before. Because I did not want to see what would show as a result without understanding it, and I was scared to see the result that was created by what I did not understand.
In this writing, at least I can find out whether this shows correct number or not in this sample.
My sample looks like below.
Data model
Customer table
Sales table
And I try to create a calculated table that looks like something like below.
The below is a table visualization with the measure [YTD measure: ].
Let's go to DAX Studio and let's try to write CALCULATE ( CALCULATE (...) ), and let's try to create a calculated table.
As seen above, V1 (one CALCULATE) and V2 (two CALCULATEs) are the same.
V3 is what I usually write until today, because it has only one CALCULATE and it is clearly for the context transition.
The conclusion is that there is no difference between one CALCULATE and two CALCULATEs inside ADDCOLUMNS function. However, it is not so easy to 100% understand why the results are the same.
V1 -> One CALCULATE is used for creating YTD numbers. Then what is causing context transition? Is one CALCULATE functions for the two purpose?
V3 is relatively easy to understand, but it is quite long to write.
In my opinion, the best way to create a correct and expected result is, re-use the already-created-measure, like below. It even does not need to write CALCULATE for making context transition, because the measure itself is already wrapped up with it to create context transition.
Not so long time ago, there were some requests in the community that the solution had to be written only one-lengthy-DAX-furmula. I could not know the reason, but when I tried to create two or three measures to support the final solution formula (calculated table, calculated column or calculated measure), the follow up question was how could I combine all measures and made it into only one DAX formula.
Writing many supporting measures is sometimes good, but I cannot say it is always the best practice.
However, I can confidently say that if you do not understand one lengthy formula, then creating some supporting measures to have a final solution is the best option to go.
I hope this blog post can provide some ideas on how to have fun in structuring and managing your measures in your dataset.
Comments