As I continue my journey with calculation groups I realize that fewer and fewer people may be interested in what I write, but then I think, «so what?» So here I am.
My script to create a Time Intelligence Calculation Group based on Daxpatterns.com definitions got a lot of attention (for my standards, anyway), so I kept on thinking if there’s anything that would go nice with it.
Indeed, in the latest iteration of the script, there are two measures that aim at providing some sort of dynamic labeling. «Label as Value Measure» is a measure that when in the scope of a Time Calculation of the calculation group, will show the appropriate label taking into account the filters on the date table. For instance, if you select YTD calculation item and year 2008, the measure will show «2008 YTD». And so on. (Label as Format String does the same but using the format string expression so it can be used as sort of dynamic axis labels if you want to place several calculations as the X-axis, but we’ll focus on «Label as Value Measure» which has more general applications)
If you plan on using just a single time calculation for a single line chart, the functionality out of the box is good enough. You can put the measure as a dynamic title and all will be fine. The thing gets trickier if you want to show two time calculations as series of the line chart, say Current Year and Previous year. One possibility is creating a calculated table with the Label as Value measure in a column so you can use as legend or in the axis like I explained in this post, but if you are not having performance issues that is a lot of trouble and a waste of time and resources. Another possibility and a good practice from a visualization point of view is to somehow name the series on top of the series, not as a legend.
Of course, Power BI does not offer any native option label series on the line — data labels just show values, by default. However, there’s a cool functionality that let’s you tinker with that. You know where I am going. Calculation groups. Once again. Calculation groups let you tinker with the measure’s in the scope, but also with their format strings. In general you might want to do that if for example your calculation group takes a measure and calculates its YOY %, you’ll want to change the format string to display as percentage. So far so good, but how is this related to showing dynamic labels on the series? Well, you can stretch this format string functionality to show pretty much anything you want on the data labels. But for that you need to know a bit how format strings work. First thing is that your formatstring expression must, er, be a string. Ok. But format strings are weird, because characters have special meanings, 0 means any number, # means a number if necessary and much much more. So if you just put what you want to see as a string, chances are that some character will be one of these special characters and everything will get messed up. Now, it is possible to include listeral strings inside your format strings, but you need to protect them. How? just putting them inside double quotes. But wait, we are already writing something between double quotes. How are we supposed to say «hey this double quote is not the closing double quote, but rather a double quote that I want to be displayed inside my string» ? the answer is double double quotes. What? yes. If I have number of watts for instance, and I want to include the unit in the format (keeping the whole thing numeric), then my formatstring would be «0 «»W»»». I’m not sure if W has any meaning inside a format string. Maybe not and you can just write » 0 W», but better safe than sorry, right?
And then there’s another thing that is good to know about formatstrings, they can be composed up to 4 different strings themselves, separated by a semicolon. Why is that? well, if you don’t specify otherswise (we’ll not get into that) the strings correspond to Positive numbers;Negative Numbers; Zero; Text. By default negative numbers have a – sign at the front that we need to be aware of in case it appears.
Anyway, now that we’re more familiar with format strings let’s see how we can get to the point we want.
First of all, I want a way to hide all the labels except the last one. Then I want the last one to be the result of «Label as Value Measure» when in the scope of the Time Calculation.
My first approach was to create a new calculation group and make a calculation item like
= SWITCH ( TRUE (), SELECTEDMEASURENAME () IN VALUES ( 'Time Intelligence Affected Measures'[Measure] ), VAR maxDateInVisual = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR maxDateInDataPoint = MAX ( 'Date'[Date] ) VAR result = IF ( maxDateInDataPoint = maxDateInVisual, [Label as Value Measure] ) RETURN IF ( ISBLANK ( result ), ";;;", """" & result & """" ), SELECTEDMEASUREFORMATSTRING () )
That didn’t work. Then I analyzed the code and realized that this calculation needs to happen in the scope of the Time Calculation, otherwise [Label as Value Measure] will not have the value I want it to have. In other words we need our new calculation group to have a lower number of precedence than the time calculation one. So far so good. I changed the precedence numbers and bam: It was working. Wait, not always.
First of all, there was a hyphen sometimes in front of the dynamic label… weird. Oh! it’s the negative sign!! it’s a bit weird that it gets combined with my format string, but anyway, it’s happening, so let’s be more explicit about the formatting.
= SWITCH ( TRUE (), SELECTEDMEASURENAME () IN VALUES ( 'Time Intelligence Affected Measures'[Measure] ), VAR maxDateInVisual = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR maxDateInDataPoint = MAX ( 'Date'[Date] ) VAR result = IF ( maxDateInDataPoint = maxDateInVisual, [Label as Value Measure] ) RETURN """" & result & """;""" & result & """;""" & result & """;""" & result & """", SELECTEDMEASUREFORMATSTRING () )
This made the negative sign disappear. Now, looking carefully calc Items such as CY, PY, even YOY worked, but YOY% did not. That’s odd. For all the percentage-formatted calculation items, the effect of the labels calculation group disappeared completely.
I thought for a while. Then it made sense, Time Calculation calc group is applied first, but that also means that when the evaluation starts, it is the outermost calculate thing so to say. This took me a while to understand — this video by Alberto Ferrari definitely helped. Indeed, for all the items that worked, the format string expression is just SELECTEDMEASUREFORMATSTRING() so it respects any modifications of the format string that may have been done (simplified expression).
= SWITCH ( TRUE (), SELECTEDMEASURENAME () IN VALUES ( 'Time Intelligence Affected Measures'[Measure] ), SELECTEDMEASUREFORMATSTRING (), SELECTEDMEASUREFORMATSTRING () )
However, for the percentual time calculations the time calculation item sets an explicit formatstring (simplified expression)
= SWITCH ( TRUE (), SELECTEDMEASURENAME () IN VALUES ( 'Time Intelligence Affected Measures'[Measure] ), "#,##0.#%", SELECTEDMEASUREFORMATSTRING () )
So I needed a way of warning the Time Calculation calc group that some format string tuning had taken place and that I don’t want it to change the format string that is already in place. It must be something present in the format string for any value, and yet something that does not mess the format string itself….
I decided I would use the famous zero-width space unicode char (UNICHAR ( 8204 )) that is often used to sort things that look the same (to the dax engine at least) but must be treated differently. So the Labels calculation item ended up looking like
= SWITCH ( TRUE (), SELECTEDMEASURENAME () IN VALUES ( 'Time Intelligence Affected Measures'[Measure] ), VAR maxDateInVisual = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR maxDateInDataPoint = MAX ( 'Date'[Date] ) VAR result = IF ( maxDateInDataPoint = maxDateInVisual, [Label as Value Measure] ) RETURN UNICHAR ( 8204 ) & """" & result & """;""" & result & """;""" & result & """;""" & result & """", SELECTEDMEASUREFORMATSTRING () )
And of course modifying the format string expression of the time intelligence calc group to
= SWITCH ( TRUE (), SELECTEDMEASURENAME () IN VALUES ( 'Time Intelligence Affected Measures'[Measure] ), IF ( FIND ( UNICHAR ( 8204 ), SELECTEDMEASUREFORMATSTRING (), 1, - 1 ) <> -1, SELECTEDMEASUREFORMATSTRING (), "#,##0.# %" ), SELECTEDMEASUREFORMATSTRING () )
And this worked beautifully!
There is one last but that I can’t seem to overcome — if you select two time calculations, in general the labels display fine. Only when both are % time calculations, then one of the two does not display — it’s not random, always the same one, so there might be a reason lurking in there, but haven’t figured that out yet. If you want to give it a go, be my guest. Download the file and let me know if you find the reason and solution if you have it!
Thanks for reading all the way ‘till the end.
The Challenge has been solved! and it was anot a DAX thing after all. As Ricardo Rincón pointed out to me, the label is hidden because of the size of the chart and the label. Here you can see it
You can now create the calculation group with a script
grab it from the github repository right here
You can see how to use the script in this long and awful sounding video. It starts with the script of Time Intelligence – The Smart Way, and by moment 16:00 it goes through with the script for dynamic labels