Dynamic formatting for measures arrived in Power BI just before summer 2023. This allows developers to specify the format string that will be used for a particular measure when used in the report. In this blog, I’ll share what this looks like, and a little bit of code that I’ll be implementing for many of my customers in the future.

What does this mean?

It effectively means you can choose how a measure will be displayed in your report programatically, using DAX. This gives the developer all kinds of flexibility, including the ability to generate formats from user selections. In this blog, I’ll show you how to dynamically format values to thousands / millions / billions automatically, but also giving the user the ability to oversteer the selection.

Microsoft development

Before I come to that, I want to reflect on the development process at Microsoft. Things move fast with Power BI, and we often find ourselves working with features which have bugs or aren’t quite finished yet. It’s the price you pay for having things now. When this feature was released before summer, all the documentation from Microsoft mentioned you should always turn off your Y axis in visuals. This means you could use dynamic formatting, but you had to forfeit the use of your Y axis. This was a deal breaker for me, but having access to the people developing the Power BI product on X was a lifesaver.

Here, the axis was formatting in thousands (as the lowest value was 980,400), but the axis was showing 800000 K. I checked my report recently, and like magic, the axis was working properly!

Ok, Ok, just show me the code.

To implement this, I created a simple format table, that looks like this:

I order the Format column using the FormatOrder column. I then select Dynamic Formatting on my measure, and paste in the following code:

VAR vMeasure =

    SELECTEDMEASURE ()

VAR vValgtFormat = SELECTEDVALUE(Hlp_Format[Format])

VAR vFormat =

    SWITCH (

        TRUE (),

        vValgtFormat = "Billions", "##0,,,.00 Bn", 

        vValgtFormat = "Millions", "##0,,.00 Mn",

        vValgtFormat = "Thousands", "##,0,.0 K",

        vValgtFormat = "No Format", "#,0",

        ABS ( vMeasure ) >= 1000000000, "##0,,,.00 Bn",

        ABS ( vMeasure ) >= 1000000, "##0,,.00 Mn",

        ABS ( vMeasure ) >= 1000, "##,0,.0 K",

        "#,0"

    )

RETURN

    vFormat

This DAX statement will format the measure firstly based on the user selection in the slicer. If there is no selection or the user selects more than one value, the values will be formatted dynamically based on their size.

Here’s how the table looked with whole number formatting:

Here is after with a new slicer, with a selection on ‘Millions’:

If the user selects more than one value, the dynamic formatting kicks in:

And that’s it. It might not be the most groundbreaking development coming to Power BI right now, but it made me very happy. This could previously be done with calculation groups, but I found them to be slower performing and more clumsy to work with (for example, you’d have to have a calculation group on top of a matrix and always have it drilled down).

What do you think?