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:

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 give me claps… as many as you can. :-)

This article was published in Microsoft Power BI publication, subscribe it for more interesting Power BI Tutorials, Tips and Tricks.

You can subscribe to read ours and thousands of other inspiring articles on Medium.com with only one subscription fee. This is where Medium.com makes the difference.