Hello,

today I’m not writing any type of tutorial. I just want to share a weird behavior I have found in calculation groups, hoping that those that really know about the inner workings can help us comprehend why they behave like that. Calculation groups can be seen as groups of pairs of DAX expressions that replace measures and their format strings when they are in a filter context where they participate. There’s quite a few articles that explain calculation group precedence, but when a calculation item is applied, how are the values of SELECTEDMEASURE and SELECTEDMEASUREFORMATSTRING evaluated? are they the values and format string *before* anything is applied? What happens if we include SELECTEDMEASURE inside the format string expression or SELECTEDMEASUREFORMATSTRING inside the value expression? If your head is about to explode, you are not alone.

In the different posts of this blog I’ve reached different conclusions in different articles, so today I want to present two examples to deepen in this topic — during this process I hope to understand it more!

Example 1

This is the setup. I have a measure with a constant value of 1 and a format string that is just «0». Then I have  format string expression that if the measure is 1 it will cast it as a percentage and otherwise as a currency. Yet the value expression will overwrite the value depending on the format string is. Sounds confusing? It is. The DAX of the whole thing looks like this.

MEASURE 'Sales'[just 1] = 1
    FormatString = "0"

CALCULATIONGROUP 'Precedence Check'[Precedence Check]

    CALCULATIONITEM "check" =
        SWITCH (
            TRUE (),
            SELECTEDMEASUREFORMATSTRING () = "0", 10,
            SELECTEDMEASUREFORMATSTRING () = "0%", 20,
            SELECTEDMEASUREFORMATSTRING () = "0$", 30
        )
        FormatString = IF ( SELECTEDMEASURE () = 1, "0%", "0$" )

Now if you feel sharp today, you can try to guess what will come out of it. But it’s even hard to just try to understand what happens when we put the measure in a card and apply the calc item.

Ok, now that I look at it is even harder than I thought. The only way we can reach this result is like this:

• We are evaluating first the value expression
• However we need to get the «selected measure format string» to do it
• If it took the value directly from the measure, the value would be 10 because the format string of the original measure is just «0». However that «SELECTEDMEASUREFORMATSTRING» forces the evaluation of the format string expression.
• That expression checks the original measure and says «ok, the original value is 1» so the new formatstring is «0%»
• So we are now back at the value expression and with that «0%» format string we decide a value that will override the original value of the measure, since we got «0%» as format string the new value is 20
• Yet we are not finished, the format string expression was evaluated as part of the value expression, but it looks like there’s a new evaluation now that we have completed calculation of the value.
• When we evaluate again the format string expression, SELECTEDMEASURE is no longer the original measure, but rather the value of the expression *after* the value expression has been evaluated. So this time around the format is not «0%» but rather «0$»

If you have a simple format string expression, is easy that you end up thinking that selected measure format string was evaluated first.

Let’s have a look at another example now

Example 2

This one is much easier. I have a measure of constant value of 2000, and a calculation item with a value expression that will divide the original value by 1000 if the value is larger than 1000. The format string expression will add a «k» to the format string of the measure if the value of the measure is larger than 1000 and «kk» otherwise (to make it easier to see what is going on).

The dax looks like this:

MEASURE 'Sales'[just 2000] = 2000
    FormatString = "$ #,##0"
CALCULATIONGROUP 'Precedence Check'[Precedence Check]

    CALCULATIONITEM "Check 2" =
        IF ( SELECTEDMEASURE () > 1000, SELECTEDMEASURE () / 1000, SELECTEDMEASURE () )
        FormatString = IF (
            SELECTEDMEASURE () > 1000,
            SELECTEDMEASUREFORMATSTRING () & "'k'",
            SELECTEDMEASUREFORMATSTRING () & "'kk'"
        )

So what do you think it will happen now?

Okay, this time is a bit easier. Here it seems clear that the value expression goes first, divides the value by 1000, so when the formatstring expression comess «SELECTEDMEASURE» has already the modified value and since that’s not larger than 1000 it get’s the «kk» sufix.

Conclusion

By just writting this blog post and forcing me to actually do the testing, I think I clarified in my head what happens.

As a general rule Value expression goes first, and then Format string expression is applied. Inside format string expression, SELECTEDMEASURE has already the modified value.

However, if inside the value expression you use SELECTEDMEASUREFORMATSTRING you will force an evaluation of the format string expression that will happen *before* the value expression and as such SELECTEDMEASURE will still be the original value, and SELECTEDMEASUREFORMATSTRING will still be the format string of the measure.

Of course there are very few use cases (even fewer outside this blog) that play simultaneously with the value and formatstring in such a recursive way, but as the Spanish saying says «knowledge does not take place»

If anyone has other explanations or even more wicked examples that invalidate my reasoning, please let me know.

If you don’t believe the screenshots I shared, download the pbix and play with them.

Please share your comments on Twitter and LinkedIn

Regards!