Hi everyone - I would like to share how to use dynamic formatting on measures in Power BI.
This has already been available by using FORMAT DAX function, however that function converts measures to text and so it could not be used on most visuals.
Here is the final result that we will get. With a selection of a measure the formatting will change (2 decimal places for Value and 0 for Volume):
I will be working on a simple sales table with data:
Tutorial - step by step:
1. Create a table with measures that you would like to select on the report. Add additional column that describes the desired measure format (you can use any format string that you would normaly use in the FORMAT() DAX function)
2. Create a Dynamic measure with switch.
3. Add a slicer and add your Measure column to it so that we can select the measure we wish to see. Select a random value in the slicer.
4. Create a visual and add a desired Dimension (in my case Country) and Dynamic Measure as Value.
You will now be able to swap the measure on the graph based on the selection from the slicer but both measures still have the same format.
5. Now we get to the hard part. You will need to add a Calculation Group using Tabular Editor.
You need to download and install it and then you will see it on the "External Tools" ribon in Power BI.
Open Tabular Editor and create a new Calculation Group. I have called mine Dynamic formatting.
Add new Calculation Item. I have called mine Format Measure.
In the Expression Editor add SELECTEDMEASURE()
In Format String Expression add SELECTEDVALUE('Measure'[Format], SELECTEDMEASUREFORMATSTRING())
This will take the measure used on the visual and format it by the definition from 'Measure'[Format]
Click Save Changes and return to Power BI
6. Include Dynamic formatting in the graph. You can do it in two ways:
a. Add the Name from Dynamic formatting to graph legend (I had some problems with graph colors with this aproach)
b. Add Name from Dynamic formatting to visual filters and just select the only value
Hello, Thank you for the guide, it helped me with a visual where I was using a Dynamic Measure.
It seems to have an effect on a lot of other visuals as well. Some measures suddenly take a different format then what I set it to.
Those are measures that have nothing to do with the ones I made it for. Sorting options dont work properly anymore because of it.
When I remove the Dynamic Formating table, it goes back to normal.
Simple example, I have a different measure that is a Percentage, but as a Gauge it simply won't show it as a percentage. In a matrix or table, it shows it as percentage, but not as a gauge.
How do i make it so that the Dynamic Formatting only works for the Dynamic Measure?
Hi - yea there are some problems with the method.
For 1 it works best if you have just one measure on the visual. If you add more they will all be formated.
As for your Gauge I'm not sure wht it is the case. Perpaps the format you are using is not the same "percentage" format as the visual requires? All I can suggest here is to use the original measure without the dynamic format option.
Hi thanks for the reply.
Oh well it still helps knowing there is a work around 🙂
Hope they will add something in the future so you can use FORMAT and that it wont be text or something
Thanks for the guide!
Do you have the .pbix for this? I'm trying something very similar but I'm not sure if it's going to work and my dataset is very large making experimentation on it extremely difficult. Thanks.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.