How to modify Dynamic Format Strings in Power BI
I was recently testing out and using the great new Power BI feature using dynamic format strings in Power BI.
What I found that currently it is not possible or easy to modify an existing dynamic format string.
In this blog post below I show you how I managed to modify the dynamic format string, so that you do not need to DELTE it and re-create it!
The only way I could find in Power BI desktop was I had to change the format, which would then delete the dynamic format string as per the warning shown below.
In my example I first created a dynamic format string for my measure called [Sales]
What I did was if the Country of United States was selected to show the dollar sign “$” otherwise do not show any currency value.
I first enabled the Dynamic format strings in the Preview features in Power BI desktop.
Next, I went into my measure, clicked on the drop down for Format and selected “Dynamic”
I then put in the following details for the Dynamic format string
It then defaulted to what was currently selected which was the Currency format
I then put in the following into my dynamic format string as shown below.
NOTE: When using the dynamic format expression, it is using DAX, so make sure to ensure your syntax and strings are as you would do for any DAX measure.
Now when I tested the changes, it worked as expected when I selected United States in the Slicer, but I realised that I wanted it to be 2 decimal points.
Instead of having to re-create the dynamic format string, to modify the dynamic format string I opened Tabular Editor and connected it to my PBIX file.
I then went to my measure [Sales] and clicked on Sales.
In the Properties window, there is a new property under Options called “Format String Expression”.
NOTE: Those familiar with Calculation groups would be familiar with this when wanting to format items in your calculation groups.
I could then click on the drop-down arrow for the Format String Expression and edit the expression as shown below
What I forgot to do was I wanted it to be to 2 decimal points so I updated the format string as shown below.
One thing to note is when using Tabular Editor, is that there is no Intellisense for the format string expression. So, you might encounter the odd error because the DAX is invalid.
I then pressed Enter and saved it from Tabular Editor back to my PBIX file.
Now when I selected United States, I can see the dollar value with 2 decimal points.
Then if I selected Australia, I would see no currency formatting and also the values to 2 decimal points.
In this blog post I have shown you how to update the dynamic format string without having to re-create it if you need to make a change.
Thanks for reading, any comments or suggestions are most welcome
[…] Gilbert Quevauvilliers tries out a neat feature: […]
There is a possibility to format it right in power bi, left to the measure string. Change from measure to format.
Do you mean have the data aligned on the right hand side?
I mean there is a possibility to make it simpler and correct formatting with the button FORMAT, which is situated left to the written measure. To switch from MEASURE to FORMAT.