Power BI Feature News — Dynamic String Format for Measures

This new feature allows dynamically format displayed values for measures in Power BI visualizations. Typically we will use it in case we would like to change format of numbers based on its value?or?based?on?currency?type. This helps to keep numbers in visualizations in compact format.

As this feature is still in preview we need first to activate it in Options menu:

Then when we choose any Measure, under Menu Measure Tools we can now set new Format — Dynamic:

Now we are able to use DAX language to format Measure values based on various conditions. The static format string the measure had before switching to Dynamic will be pre-populated as a string in the DAX formula bar.

Simple scenario — condition within DAX code

Let’s say we would like to format numbers differently based on total displayed value. For this example we can use DAX function?SWITCH:

SWITCH (
    TRUE (),
    SELECTEDMEASURE () < 1000, “$#,##0”,
    SELECTEDMEASURE () < 1000000, “$#,##0,.0K”,
    “$#,##0,,.0M”
)        

We will get this result in the visualization:

Another scenario for Measure "Data Transferred":

Code:

SWITCH (
    TRUE (),
    SELECTEDMEASURE () < 1000, "#,##0 KB",
    SELECTEDMEASURE () < 1000000, "#,##0,.0 MB",
    "#,##0,,.0 GB"
)        

Output:

Bar chart:

Advanced Scenario — Conditions in separate table

But what if we would like to use specific country format? We can do it with supporting table where we will store different country formats.

Table with country formats:

Data model:

Dynamic String Format for Measure using DAX function?SELECTEDVALUE:

SELECTEDVALUE (
‘Country Currency Format Strings'[Format],
“\\$#,0.00;(\\$#,0.00);\\$#,0.00”
)        

Number format will be changing automatically according its currency:

That’s it for today. I hope you enjoy this article and don’t forget to like it or share it with somebody who could be interested. :-)

You can find also some interesting Power BI tips&trick, tutorials and use cases on our Power BI blog.