What to Do When Calculation Groups Break Your Measures (PowerBI Tutorial)

Unfortunately, it does happen… You have just added a new calculation group/calculation item to your model and all of the sudden, some of the visuals on your report stop working. Usually, the main reason behind is some sort of invalid DAX in your calculation group. However, sometimes this might happen even if on a surface it appears that your DAX is perfectly fine.

The scenario that I cover in this video deals with a situation when you have added a calculation item that is expecting a number (for example you are trying to calculate a Year Over Year variance, or something like that). Or in my example I have create an even simpler one that takes the existing value of the measure and then increments it by one.

Turns out that although the DAX is very simple, if you have measures that return text values (and we often do as we need to display dynamic titles, etc.) then those measures become broken and the only way for fix them is to add a bit of code to your DAX statement to make a bit more robust as well is to introduce a naming convention to your measures so you can recognize which measures are text and which ones are not simply by looking at their name.

What I often do is either prefix or suffix the text measure name with something like “TEXT”, “Label”, “Lbl”, etc. then you can modify your DAX to follow the logic outlined below…

You can download the sample file here.

Measure expression:

Var MeasureName = SELECTEDMEASURENAME()
VAR IsIgnore = SEARCH ( "TEXT", MeasureName, 1, 0 ) > 0

RETURN
IF
(
IsIgnore,
SELECTEDMEASURE(),
SELECTEDMEASURE() + 1
)

Format String expression:

Var MeasureName = SELECTEDMEASURENAME()
VAR IsIgnore = SEARCH ( "TEXT", MeasureName, 1, 0 ) > 0 

RETURN

IF
(
    IsIgnore,
    SELECTEDMEASUREFORMATSTRING(),
    "#"
 )