Hello there, this is not an entirely new technique, but then again, maybe the use case will be useful to someone, so I’ll go ahead and put it here. Indeed the idea came once again from the whatsapp group of Power BI User Group Barcelona, great people all around. Paul shared with the rest of us a solution he brought to a question in the Power BI Community Forum. I that thread (and answering the particular use case) a few solutions involved doing it all in Power Query, while Paul brought in a DAX approach to the solution. Beyond the particular use case of the question, this raises the question: how can we show individual measures in a tabular format?
In Power BI, even if we have the measures that return the values that we want individually, it’s not straignt forward to show them in a compact table. To make matters worse, if you go through the pain of setting up multiple cards, the final report will be sluggish as it will have a high number of visuals. So what is to be done? One approach of course is what Paul shows in his solution. Apparently there are companies out there that ban the use of external tools, so if you are in this group you might want to stick with Paul’s solution. However, if Tabular Editor is not off-limits for you, I think that a calculation group solution might be easier to set up and maintain over time. Or at least let’s say it’s an alternative that you should consider.
Let’s recap the use case, we have the strong requirement to show values that are not mere transformations of the same base measures in a tabular format. In other words we are not talking about making just a column with current values, and a column with Values last year, we are speaking here about something more arbitrary that we do not want to code inside a measure. We already have the measures and we are searching for a handy way to display them in a tabular format.
The idea is to use a dynamic measure calc group to be able to call measures by name, as I showed in this post. We will set up a table with the names of the measures that we want to show in each cell, and then with the calc group we’ll replace each measure name by the actual measure. Piece of cake.
First of all we design our desired table. Let’s imagine that the CEO has requested that values should be shown like this (for some reason).
Note that the names match the names of the measures! First of all we’ll import this table into Power BI (if the CEO changes the idea and wants to swap Margin and Margin %, we’ll just need to update the excel file). While importing the table though we’ll do a couple things to be able to show the measure names as the result of a measure in the values seciton of a matrix. First add an index column (I like starting from 1, but your call). Then select the index column and select «Unpivot other columns». This should produce a table that looks like this
Before loading into the model give the table a decent name, not like me. I called it «Arbitrary Table». Now while you might be tempted to just drag all three columns to the matrix, refrain yourself and create a measure for the value. Remember the plan is to use a calculation group, and calculation groups only work on explicit measures (not the podcast, I mean the actual explicit measures). Something like this should work:
Measure Name = SELECTEDVALUE ( 'Arbitrary Table'[Value] )
Now you can build your matrix showing the measure names
Now it’s time we create the dynamic measure calc group with the «dynamic measure calc group script» (check this and other ones from this post) if we already have it as macro/quick action, just select the measures, right click, select dynamic measure, voilá! (here I just paste one of the calc items for clarity, you get the idea of what you should get after running the script)
---------------------------
-- Measure: [Dummy Measure]
---------------------------
MEASURE 'Dynamic Measure'[Dummy Measure] = BLANK()
Description = "Control the content of this measure by selecting values from Dynamic Measure."
---------------------------------------
-- Calculation Group: 'Dynamic Measure'
---------------------------------------
CALCULATIONGROUP 'Dynamic Measure'[Dynamic Measure]
CALCULATIONITEM "Margin" =
IF(
ISSELECTEDMEASURE( [Dummy Measure] ),
[Margin],
SELECTEDMEASURE( )
)
FormatString =
IF(
ISSELECTEDMEASURE( [Dummy Measure] ),
"#,0.00\ ""€"";-#,0.00\ ""€"";#,0.00\ ""€""",
SELECTEDMEASUREFORMATSTRING( )
)
So this calc group has a calculation item for each 4 measures, and the calculation item name is exactly the name of the measure. Thus, when we apply a calculation item of this calc group, in a way we are calling measures by their name, which is quite cool.
Now we need a calc group (of higher precedence) that will take the value of our «Value as measure» measure and use it to apply the calc item to the dummy and thus replace the measure name by the actual measure!
Create a new calculation group (by default this will be already of higher precedence of the calc group we just created) and add a calculation item «measure name to value». First we check if measure in the scop is indeed «Measur Name» measure, and if so, we take the value and apply the calculation item with the same name on «Dummy Measure», something like this:
---------------------------------------------
-- Calculation Group: 'Measure Name To Value' (V1)
---------------------------------------------
CALCULATIONGROUP 'Measure Name To Value'[Measure Name To Value] Precedence = 1
CALCULATIONITEM "Measure Name To Value" =
IF(
ISSELECTEDMEASURE( [Measure Name] ),
VAR measureName = SELECTEDMEASURE( )
RETURN
CALCULATE(
[Dummy Measure],
'Dynamic Measure'[Dynamic Measure] = measureName
),
SELECTEDMEASURE( )
)
With this we should be good to go, right? Wrong. See what happens when we apply it
But why?!? Our dynamic measure calc group has format string expressions defined and all, why does it not look fine? Oh well — *think* about it! we just wrote the value expression of the calc item, not the format string expression ! Is there anything we can do about it?
The thing is a bit trickier than on the value side. When we apply a calculation item in a DAX expression we get by default the result of the value, not the format string. The only way I figured out to get the format string is with –yes– another calculation group! This new calculation group needs to live between the two that we already have, it needs to be the cheese of the sandwich. We want it to «capture» the format string resulting from the dynamic measure format string and move it to the value expression so that we can retrieve it inside a DAX expression. Let’s do it!
First we’ll move the precedence of the «Measure Name To Value» calc group to say, 10. Now we create a new calc group «Get Format String» and we set the precedence value to 5. The only calculation item has a very simple value expression, just «SELECTEDMEASUREFORMATSTRING()» In other words, take the format string of the measure in the filter context and show it to me. Here’s the calc group:
-----------------------------------------
-- Calculation Group: 'Get Format String'
-----------------------------------------
CALCULATIONGROUP 'Get Format String'[Get Format String] Precedence = 5
CALCULATIONITEM "Get Format String" = SELECTEDMEASUREFORMATSTRING()
Now we can go back to the «Measure Name To Value» calc group and write the format string expression. We’ll copy the value expression and make a couple modifications, first, we’ll wrap the CALCULATE with the new calculation item to convert the dynamic measure to a format string and of course the 3 argument of IF will be SELECTEDMEASUREFORMATSTRING() instead of SELECTEDMEASURE(). Something like this:
---------------------------------------------
-- Calculation Group: 'Measure Name To Value' (V2)
---------------------------------------------
CALCULATIONGROUP 'Measure Name To Value'[Measure Name To Value] Precedence = 10
CALCULATIONITEM "Measure Name To Value" =
IF(
ISSELECTEDMEASURE( [Measure Name] ),
VAR measureName = SELECTEDMEASURE( )
RETURN
CALCULATE(
[Dummy Measure],
'Dynamic Measure'[Dynamic Measure] = measureName
),
SELECTEDMEASURE( )
)
FormatString =
IF(
ISSELECTEDMEASURE( [Measure Name] ),
VAR measureName = SELECTEDMEASURE( )
RETURN
CALCULATE(
CALCULATE(
[Dummy Measure],
'Dynamic Measure'[Dynamic Measure] = measureName
),
'Get Format String'[Get Format String] = "Get Format String"
),
SELECTEDMEASUREFORMATSTRING( )
)
Awesome, now let’s look how beautiful it looks…
WHAT?
hmmm… here the hint comes at the very end of the error message: «… which is of type String». Power BI is very picky about format strings. It wants to be completely sure that it will get a string from the expression. However, calculate has a type variant output, and so Power BI does not trust it will get a string from there. It does not matter if you have a well crafted calc group that certainly will provide a string, Power BI does not trust you either. But this has a simple work around, you need to wrap the thing in a function that always returns a string, such as format. I already used this trick in my very first blog post, on using calculation groups in calculated tables. Here’s the calc group with this small modification:
---------------------------------------------
-- Calculation Group: 'Measure Name To Value' (v3)
---------------------------------------------
CALCULATIONGROUP 'Measure Name To Value'[Measure Name To Value] Precedence = 10
CALCULATIONITEM "Measure Name To Value" =
IF(
ISSELECTEDMEASURE( [Measure Name] ),
VAR measureName = SELECTEDMEASURE( )
RETURN
CALCULATE(
[Dummy Measure],
'Dynamic Measure'[Dynamic Measure] = measureName
),
SELECTEDMEASURE( )
)
FormatString =
IF(
ISSELECTEDMEASURE( [Measure Name] ),
VAR measureName = SELECTEDMEASURE( )
VAR variantFormatString =
CALCULATE(
CALCULATE(
[Dummy Measure],
'Dynamic Measure'[Dynamic Measure] = measureName
),
'Get Format String'[Get Format String] = "Get Format String"
)
VAR stringFormatString = FORMAT( variantFormatString, "@" )
RETURN
stringFormatString,
SELECTEDMEASUREFORMATSTRING( )
)
so let’s see if this works…
But whyyyyy?!?
In this case I troubleshot first by using the format string expression as value expression. Here, no issues.
But what is going on then? Why is it not returning the same thing when moved to the format string expression?? Well, the only possible culprit is «SELECTEDMEASURE()» We are using that for both expressions, assuming that they are both applied simultaneously, but what if they are not?? What if formatstring is applied *after* the value expression? To check that I tried now replacing instead the format string expression with «»»» & SELECTEDMEASURE() & «»»» . All these double quotes are to format whatever is returned by selected measure as an arbitrary string inside the format string (today everything is arbitrary apparently). Actually since I do not use SELECTEDMEASURE outside the double quotes, it should only show the SELECTEDMEASURE value. And yet…
Now I was not sure if any format string was applied at all or it was, but SELECTEDMEASURE was returning the value *after* the value expression… to be sure I tested again with
«»»_» & SELECTEDMEASURE() & «_»»». If the format string was being applied I should see these underscores…
Ok, so good news is that format string was being applied. The bad news is that they are being applied *after* the value expression. which is odd, because in another blog post I reached the opposite conclusion…
Anyway, using SELECTEDMEASURE() in both value and format string expressions appears not to be a good idea. Are we doomed? no we are not. If we go back to the origin of the use case, it’s all about display. In other words, as long as it looks right is all good. Even in another set of words, nobody cares if what we are seeing is a number or a text… ok, it’s dirty but it works.
We’ll create a new calculation item in the «Get Format» calculation group which will call «Value to Text» (basically because it’s already in the right precedence and goes about the same thing). This calculation item will have also a very simple value expression to take whatever measure is in the context and apply it’s own format string to produce a formatted string.
-----------------------------------------
-- Calculation Group: 'Get Format String'
-----------------------------------------
CALCULATIONGROUP 'Get Format String'[Get Format String] Precedence = 5
CALCULATIONITEM "Get Format String" = SELECTEDMEASUREFORMATSTRING()
CALCULATIONITEM "Value To Text" =
FORMAT(
SELECTEDMEASURE( ),
SELECTEDMEASUREFORMATSTRING( )
)
Now we can get rid of the format string of our «Measure Name To Value» calculation item, and instead use the new calculation item from «Get Format String» calculation group in the value expression:
---------------------------------------------
-- Calculation Group: 'Measure Name To Value' (V4)
---------------------------------------------
CALCULATIONGROUP 'Measure Name To Value'[Measure Name To Value]
Precedence = 10
CALCULATIONITEM "Measure Name To Value" =
IF(
ISSELECTEDMEASURE( [Measure Name] ),
VAR measureName = SELECTEDVALUE( 'Arbitrary Table'[Value] )
RETURN
CALCULATE(
CALCULATE(
[Dummy Measure],
'Dynamic Measure'[Dynamic Measure] = measureName
),
'Get Format String'[Get Format String] = "Value To Text"
),
SELECTEDMEASURE( )
)
FINALLY!
well, values are formatted yes. However, since they are text they are aligned to the left, and I didn’t find a way to change it, so it looks a bit strange, but better than nothing isn’t it? I think that SELECTEDMEASURE() should return the value *before* the value expression of the calc item is applied, but as they say, it is what it is.
—
UPDATE (2022-04-10): After publishing this blog post, Ricardo Rincón (aka Nexus150, Power BI Quizz champion and great guy from Power BI User Group Barcelona) suggested that instead of relying on SELECTEDMEASURE I get the value using the definition of the measure instead. So I took V3 of the calc group and modified it as follows:
---------------------------------------------
-- Calculation Group: 'Measure Name To Value' (V5)
---------------------------------------------
CALCULATIONGROUP 'Measure Name To Value'[Measure Name To Value] Precedence = 10
CALCULATIONITEM "Measure Name To Value" =
IF(
ISSELECTEDMEASURE( [Measure Name] ),
VAR measureName = SELECTEDVALUE('Arbitrary Table'[Value])
RETURN
CALCULATE(
[Dummy Measure],
'Dynamic Measure'[Dynamic Measure] = measureName
),
SELECTEDMEASURE( )
)
FormatString =
IF(
ISSELECTEDMEASURE( [Measure Name] ),
VAR measureName = SELECTEDVALUE('Arbitrary Table'[Value])
VAR variantFormatString =
CALCULATE(
CALCULATE(
[Dummy Measure],
'Dynamic Measure'[Dynamic Measure] = measureName
),
'Get Format String'[Get Format String] = "Get Format String"
)
VAR stringFormatString = FORMAT( variantFormatString, "@" )
RETURN
stringFormatString,
SELECTEDMEASUREFORMATSTRING( )
)
And I saved the changes back to the model…
Good news is that is showing numbers with correct format strings (and not formatted text), however numbers are still aligned to the left! WHYYYY!? then I looked at the measure on the visual. It’s still «Measure name» that returns a text, which I do not use anymore on the calc group. Maybe it’s showing on the left because the base measure is a string type measure… Let’s change it to zero and see what happens! It’s so nice I added it as gif
Awesome! now we learned something about alignment and calc groups. Finally we got what we wanted, even though it involves a lot more pain than is reasonable. But then again, this also depends on how reasonable your CEO is. If you do it without calc groups, your only way is through formatted text, so numbers will be shown on the left (or that’s my guess at least). If you want your numbers aligned to the right, calc groups is your choice. But maybe –maybe– you need to rethink your design.
— End of update 2022-04-10
UPDATE (2022-04-16): Today I received great feedback from Owen Auger (one of the greatest DAXers out there). In this tweet, he brings a better solution skipping the Get Formatstring calculation group:
---------------------------------------------
-- Calculation Group: 'Measure Name To Value' (V6)
---------------------------------------------
CALCULATIONGROUP 'Measure Name To Value'[Measure Name To Value] Precedence = 10
CALCULATIONITEM "Measure Name To Value" =
IF (
ISSELECTEDMEASURE ( [Dummy Measure] ),
VAR measurename = SELECTEDVALUE ( 'Arbitrary Table'[Value] )
RETURN
CALCULATE (
SELECTEDMEASURE ( ),
'Dynamic Measure'[Dynamic Measure] = measurename
),
SELECTEDMEASURE ( )
)
FormatString =
IF (
ISSELECTEDMEASURE ( [Dummy Measure] ),
VAR measurename = SELECTEDVALUE ( 'Arbitrary Table'[Value] )
RETURN
CALCULATE (
SELECTEDMEASUREFORMATSTRING ( ),
'Dynamic Measure'[Dynamic Measure] = measurename
),
SELECTEDMEASUREFORMATSTRING ( )
)
I highlighted in blue the genius part — you can retrieve the formatstring directly with a single calculate, no need to do convoluted stuff with extra calculation groups. You want the value? Put SELECTEDMEASURE(), you want the formatstring? put SELECTEDMEASUREFORMATSTRING(). It’s so easy once it’s shown — genius! You see that the value and format string expressions are almost the same, just changing SELECTEDMEASURE by SELECTEDMEASUREFORMATSTRING, and that’s it — so elegant. It’s more academic than something for production? maybe, but hey. I modified the PBIX file to use this definition as it’s clearly better than mine.
— End of the UPDATE (2022-04-16)
I thought of adding some more use cases, but the article is way too long at this point. Thank you for reading!
Well, after all, maybe not that easy to show measures in tabular format with the right formatting, but we learned a thing or two along the way! If you want to play around the pbix, knock yourself out
As usual, follow the conversation on Twitter and LinkedIn