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 Measurecolumn 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
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.