Format your measures with Dynamic Format Strings!
Microsoft released a new feature for Power BI called Dynamic Format Strings! Say no more to static formatting of measures or having duplicate measures just to display data in another format.
In this blog we will explain what Dynamic Format Strings are, why you should use them and how you can set them up. There are also some real-life examples added to this blog, to give you an idea in which cases they come in handy and would improve the report.
What are Dynamic Format Strings?
With this new feature we’re finally able to format a measure based on a value, a slicer or in any other conditional way!
Dynamic format strings already existed for calculation groups but are now also available for regular measures. It works in the same way as with calculation items and uses the same DAX functions.
In the past we were able to use the “FORMAT” function to format values in a conditional way. The only problem with this function was that it converted the measure to a text data type. From that moment the measure wasn’t useable anymore for visuals, where numeric values were needed (such as graphs).
With the release of the dynamic format strings, we can get around this issue: the numeric data type of the measure will be kept and only the displayed data labels will be dynamically formatted.
Why should I use Dynamic Format Strings?
Dynamic Format Strings is a powerful feature and would overall improve the quality of your Power BI reports. Here are a few reasons why you should start using this new feature.
- Improved User Experience: the use of Dynamic Format Strings makes your reports more interactive and user-friendly. Users can change the format of displayed data themselves, by selecting the preferred format. You don’t need to create multiple versions of you report anymore, to display the data in another format you can just use this new feature.
- Consistency: by setting Dynamic Format Strings on your measures, you ensure that the data will be always displayed in the same consistent and understandable manner.
- Flexibility: Dynamic Format Strings add extra flexibility to your report. Users can easily switch between formats, depending on their preferences or regional settings.
- Reduced Redundancy: with this new release we don’t need to make multiple versions of the same measure anymore to display it in another format. Dynamic Format Strings allow us to reuse measures and to display it dynamically.
- Conditional Formatting: sometimes it’s valuable to format values when they are in a certain condition. Dynamic Format Strings enable these conditional format options.
How do I set up a Dynamic Format String?
Setting up a Dynamic Format String is quite simple and only requires a few steps to take.
- Create a measure in Power BI and select it from the list in the “Data” panel.
2. Go to the “Measure tools” tab in the ribbon and change the measure format type to dynamic.
3. From the moment you change the format, you will have the possibility to toggle between the “Measure” input field and the “Format” input field. Change it to “Format” to set the dynamic format string.
4. Provide a DAX formula for the formatting of your measure. (Keep reading for some useful examples!)
5. Verify the format of the displayed data in your visuals.
6. Optional: If you would like to remove the format string, change the format type of the measure. A pop-up will come up, click “Change” to confirm the format change and the format string will be removed.
When should I use Dynamic Format Strings?
There are a lot of use-cases where you could use Dynamic Format Strings. To give you an idea, we will cover the following three examples:
- Dynamic Currency Format – Currency format based on selected country
- Dynamic Numeric Unit Format – Numeric format based on selected numeric unit
- Dynamic Temperature Unit Format – Temperature format based on selected temperature unit
Example 1 – Dynamic Currency Format
- Create a new table containing a list of countries, with their currency and the format string to format each currency.
2. Create a table with a list of yearly average exchange rates, used to convert values from one currency to another. In this example we use conversion rates from dollar to another currency.
3. Provide a date table with a year column, used to filter out the correct yearly average exchange rate. Exchange rates change over time, so it is important to use the rate of the same point in time.
4. Once the tables are created, we can create relationships to link them together in the data model.
a. Many-to-many between date table and yearly average exchange rates table, on “Year” columns.
b. Many-to-one relationship between yearly average exchange rates and table containing the format strings, on “Country” columns.
5. In this example we will calculate the total sales and will format this measure based on the currency of the selected country.
Measures to create:
- Sales Amount – Sum of total sales
b. Exchange rate – Measure which will calculate the average exchange rate
c. Currency Converted Sales Amount – Measure we use to calculate the converted sales amount and where we will set the dynamic format string.
6. Set the dynamic format string for ‘Currency Converted Sales Amount”.
7. Use the converted measure in visuals and create a slicer to select the country. Select a country to see the associated sales amount in the correct formatting.
Example 2 – Dynamic Numeric Unit Format
- Create a new table containing a list of numeric units and their format string.
2. In this example we will use the same measure as in Example 1, the “Sales Amount”. Create a new measure which we will use to dynamically format the numeric unit.
a. Unit Formatted Sales Amount
b. Set the dynamic format string for the ‘Unit Formatted Sales Amount’.
3. Use the measure in visuals and select the preferred numeric unit with a slicer.
Example 3 – Dynamic Temperature Unit Format
- Create a new table containing a list of temperature units and their format string.
2. Create a measure to calculate a temperature. In this example we calculate the average temperature in Belgium.
3. Create a measure to convert the temperature measure to another unit, based on a selection.
4. Set the dynamic format string on the converted temperature measure.
5. Now you can also create a visual to visualize the “Formatted Average Temperature” and add a slicer to select the preferred temperature unit.
In this blog we showed a few examples where we could use Dynamic Format Strings, but this could be used in many other use-cases. Dynamic Format Strings is a powerful feature to format measures dynamically. By using DAX formulas & format strings you can improve your Power BI reports and make them more comprehensible. It encourages more flexible reports and makes it more interactive for end-users. This will bring a lot of reports to a higher level and could become a common functionality for everyone’s reports.
Data & analytics consultant @ Lytix