Dynamic Format Strings for Measures


New feature alert! The April 2023 update has introduced a game-changing feature: Dynamic Format Strings for Measures. With this new addition, you can create format strings using DAX expressions to make your reports even more adaptable and user-friendly. In this blog post, we'll guide you through this fantastic new feature, how to use it, and provide an example of changing the format of a value depending on whether it's in thousands, millions, or billions.

Turning on Dynamic Format Strings

To use the Dynamic Format Strings feature while it's still in preview, you'll need to enable it in Power BI Desktop. To do this, follow these steps:

  1. Open Power BI Desktop.
  2. Go to 'File' > 'Options and settings' > 'Options'.
  3. Under 'Preview features', check the box next to 'Dynamic format strings'.
  4. Restart Power BI Desktop for the changes to take effect.

What Are Dynamic Format Strings for Measures?

Previously, Power BI only allowed static format strings such as Currency, Whole Number, Decimal, etc., or custom static format strings like "dd MMM yyyy" for dates. With dynamic format strings, you can now use DAX expressions to create format strings that change based on various contexts within your report. This is especially useful for situations like currency conversions or displaying values in different formats based on their magnitude.

How to Use Dynamic Format Strings

  1. Click on your measure in the Data pane.
  2. In the Measure tools ribbon Format dropdown, choose "Dynamic".


A new dropdown will appear to the left of the DAX formula bar. By default, it will be on "Format", and the previously selected static format string will be pre-populated.

  1. Delete the static format string and use a DAX expression for your dynamic format string - for example:

Displaying Values in Thousands, Millions, or Billions

Suppose you have a measure called 'MyMeasure' and you want to format the values differently depending on whether they are in thousands or millions. You can achieve this using the following DAX expression for the dynamic format string:


    TRUE (),

    [MyMeasure] >= 1000000, "£#,##0,,.00M",

    [MyMeasure] >= 1000, "£#,##0,.00k",



Like so:


This expression will format the value with  an "M" for millions, a "K" for thousands, and no units at all based on the value range.


Dynamic format strings, though still in preview, unlock new possibilities for making your Power BI reports more flexible and contextual. Experiment with this feature and discover the potential it offers in creating visually appealing and highly informative reports.

Remember to keep an eye on updates, as this feature will likely become more refined and integrated into Power BI as it moves out of the preview stage. Happy formatting!


Thanks & feedback

Found this super-helpful, because the other guide I found didn't work. I have 4 metrics to switch through, 3 are percentages but one is currency, and the X axis is from a disconnected table (unsure if that's relevant).

So my measure looked like this:
Switch Metrics = SWITCH ([Selected Score Metrics],
"Metric 1", [Metric 1],
"Metric 2", [Metric 2],
"Metric 3", [Metric 3],
"Metric 4", [Metric 4])
Enterprise DNA has a piece on this here: https://forum.enterprisedna.co/t/different-formatting-types-for-two-switch-outcomes/681 - but that didn't work. It plotted all values at the same number, the tooltip didn't work so I don't know what number it was imagining.

Here's what didn't work:
Switch Metrics = SWITCH ([Selected Score Metrics],
"Metric 1", FORMAT([Metric 1], "Percent"),
"Metric 2", FORMAT([Metric 2], "0.0%"),
"Metric 3", FORMAT([Metric 3], "Currency", "en-gb"),
"Metric 4", FORMAT([Metric 4], "0.0%"))

I tried with and without the "en-gb", and I tried the currency string as "£#,##.00" too. Interestingly, in a dynamic format measure, it doesn't seem to accept the second argument for a format string (so setting locale to "en-gb" for instance).

Here's the working dynamic format string:

SWITCH ([Selected Score Metrics],
"Metric 1", "0.0%",
"Metric 2", "0.0%",
"Metric 3", "£#,##.00",
"Metric 4", "0.0%")

Thanks again.

Add new comment

The content of this field is kept private and will not be shown publicly.