Blog

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

Image

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.

3. 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:

SWITCH (

    TRUE (),

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

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

    "£#,##0"

);

Like so:

Image

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.

Conclusion

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!