Calculation Groups biggest contribution is to help us reduce the number of measures that we have to create and manage. However, sometimes, me might want measures again… Of course we can forgo the calc group altogether, but we might want to keep the centralized logic while having actual measures, and not measures + a filter as we usually do with calculation groups. The most relevant use case that comes to mind is when we do not want our calculation items to travel to the tooltip or drill through pages, as I discussed in this post not long ago. I guess that for most use cases, creating the measures manually is not the end of the world, but hey, scripting is fun and is cool, so let’s automate that a bit.
Actually months ago Stephen Maguire and I DM’d each other about a script that he had built that worked perfect, as long as you did not have complex expressions in the Format Expression pane of your calc items. Getting the value when you apply a calculation item is straightforward — CALCULATE([measure], Calc Group[Column] = «Calc Item Name»). But what happens if you want to get the resulting format string? It would be cool to have a CALCULATEFORMATSTRING function, but I’m sure there are plenty of other use cases that need more attention that this … and hey, we might not have that function, but what do we have? Yes, we have calculation groups!
The strategy here is to create an auxiliar calculation group with the highest precedence that its sole purpose is to extract the formatstring and return it as a value. Thus it has a single calculation item, and we’ll hide it and name it something that will make it easier to clean it up before going to production or updating changes there.
CALCULATIONGROUP 'DELETE AUX CALC GROUP'[Name] Precedence = 11 Visible = FALSE CALCULATIONITEM "Get Format String" = SELECTEDMEASUREFORMATSTRING ()
With this calc group now we can evaluate queries that will return the resulting format string, and use it to build the measure! Indeed in my last post I also used this «query during run-time» thing to do stuff with the reusult of a dax query. Here we’ll launch a query for each calculation item and measure pair.
But not so fast! There is one little bummer. You cannot use a calculation group in a DAX query before you have saved back to the model and recalculated your model… Oh well. The script we’ll check if you hace the auxiliar calc group and if you don’t it will create it for you — and then will tell you to save back to the model and recalculate. Now you can run again the model while selecting the base measures you want to use together with a calculation group of your choice. Well, your choice if you have more than one calculation group (other than the auxiliar one). If you only have one it will assume that’s the one you want and will go ahead with the measure creation.
Each measure gets it’s tidy and nice display folder where all its «sister measures» will be stored.
Well, but enough of all this talking (all of it) and yes, let’s see how this goes.
- First of all grab the script from github
- Paste it into the «Advanced Scripting» / «C# Script» tab of your favourite Tabular Editor flavor.
- Store it as «Custom Action» / «Macro» if you so desire (Adam Saxton voice in this last bit). Make sure to select Measure
- Select your target measures and run it! (or right click and select the alrady available macro if you saved it)
(Alternatively as custom action <– recomended as it will stay in your toolbelt!)
- On the first go it will ask you to save back to the model and refresh
- You will see the hidden calc group
- Don’t forget to recalculate from Power BI Desktop
- Go back to Tabular editor and run again the script (or Custom action). If you have more than one calculation group (besides the auxiliar one) it will ask you to choose among them.
- This time it should create your measures with the correct format string, right?
- Here, if your base measure has no defined format string, the process will fail, so I put a check that you should have proper format strings on your base measures. Since it’s considered to be a good practice to have proper format strings, we’ll call it a feature insted of a bug.
- Let’s do it one more time (this one for real) — run the script/custom action and bam!
As you see we get two display folders (one for each measure) and each of them has all the measures that are just CALCULATE([measure], Calc Group[Column] = «Calc Item Name») but the beauty here is that the format string is the one that would result if you applied the calculation item on a visual.
For the record, this is the format string expression of the YOY% Calculation Item (value expression has been removed for clarity):
----------------------------------------- -- Calculation Group: 'Time Intelligence' ----------------------------------------- CALCULATIONGROUP 'Time Intelligence'[Time Intelligence] Precedence = 4 CALCULATIONITEM "YOY%" = SWITCH ( ... Description = "Year-over-year%" FormatString = SWITCH ( TRUE (), SELECTEDMEASURENAME () IN VALUES ( 'Time Intelligence Affected Measures'[Measure] ), IF ( FIND ( UNICHAR ( 8204 ), SELECTEDMEASUREFORMATSTRING (), 1, - 1 ) <> -1, SELECTEDMEASUREFORMATSTRING (), "#,##0.# %" ), ISSELECTEDMEASURE ( [Label as format string] ), """" & /*YOY%*/ VAR ValueCurrentPeriod = SELECTEDVALUE ( 'Date'[Year] ) VAR ValuePreviousPeriod = /*PY*/ IF ( [ShowValueForDates], CALCULATE ( SELECTEDVALUE ( 'Date'[Year] ), CALCULATETABLE ( DATEADD ( 'Date'[Date], -1, YEAR ), 'Date'[DateWithSales] = TRUE ) ) ) VAR Result = IF ( NOT ISBLANK ( ValueCurrentPeriod ) && NOT ISBLANK ( ValuePreviousPeriod ), ValueCurrentPeriod & " vs " & ValuePreviousPeriod & " (%)" ) RETURN Result & """", SELECTEDMEASUREFORMATSTRING () ) Ordinal = 3
If anyone finds a way to evaluate the resulting format string without the auxiliar calculation group, please be my guest. To create measures from a calculation group with correct format is not easy if the expressions look like the one above. So far this is the best approach I found that produces the correct format string. If format string is not important in your use case then there’s no reason to complicate things so much, but hey, since it’s already done why not give it a try? You never know when you’ll need the right format string.
Oh and don’t remember to delete the calculation group once you go to production! If you ever need it again you just need to run the script once again!