Dynamic Format String for measures in Power BI

By the help of dynamic format strings for measures we can format a measure value based on a slicer selection or any other conditional way. To achieve this, we need to create a DAX expression which determines what format string a measure will use.

The FORMAT function can also be used in a measure DAX expression to conditionally apply a format string, but the drawback is if the measure was a numeric data type, the use of FORMAT changes the measure to a text data type. As a text data type the measure is then no longer usable as values in visuals. To maintain the measure as a numeric data type and conditionally apply a format string, we can now use dynamic format strings for measures.

Let’s we have the following table named Countries.

Let’s follow the steps to learn and demonstrate the use of dynamic format strings:

Step 1: Let’s create the measure “GDP Measure” first.

DAX

Step 2: Let’s add the measure to the table visual.

Now we want to format the “GDP Measure” based on the Country value, to achieve this.

Step 3: To add a dynamic format string to a measure, click on the measure in the Data pane. In Measure tools ribbon, expand the Format list box. Then choose Dynamic.

Now a new list box should appear to the left of the DAX formula bar with Format selected. This list box is how I can switch between the measure DAX expression and the dynamic format string DAX expression. The static format string the measure had before switching to Dynamic will be pre-populated as a string in the DAX formula bar.

Replace that string with the below DAX expression, to conditionally format the measure.

DAX

SWITCH(
    SELECTEDVALUE(Countries[Country]),
    "Brazil", "R$ #,##0",
    "China", "¥ #,##0",
    "Germany", "€ #,##0",
    "USA", "$ #,##0",
    "India", "₹ #,##0",
    "#,##0.0"
)

The above dax formats the measure values based on the selected country value.

We can see in the below image that the measure values are formatted.

In the image we can see that we have selected the Format from the dropdown to specify the formatting expression. So, switch back to the measure expression, just select Measure from the dropdown.

Let’s create another measure “Check Type Measure” and add it to a card visual.

DAX

Check Type Measure = ISNUMBER([GDP Measure])
// It returns True

// ISTEXT([GDP Measure])
// It returns False

The output of the above code is shown below:

Let’s create another table named CurrencyCodes.

In the Model tab we have created a relationship between the Countries and CurrencyCodes based on Country column. Please note it is bi-directionally filtered.

Now let’s modify the formatting dax expression with the following DAX expression:

DAX

SELECTEDVALUE (
    CurrencyCodes[Format],
    "#,0.00;(#,0.00);#,0.00"
)

The output of the above code is shown below:

Here, SELECTEDVALUE DAX Function:

• Returns the single value from a column if only one value is selected or filtered.
• If no value or multiple values are selected, it returns the alternate result (the default value we specify).
• "#,0.00;(#,0.00);#,0.00": This is the default value returned if none or multiple values are selected in the [Format] column. It represents the format with:
  • Positive numbers: 1,000.00
  • Negative numbers: (1,000.00)
  • Zero: 0.00

Remove the Dynamic Format String

To remove the dynamic format string and return to using one of the static format strings:

Step 1: Select the measure in the Data pane and then go to the Measure tools ribbon and expand the Format list box.

Step 2: From the list pick any other format option.

A dialog will appear asking if we want to proceed as there is no undo to this action. Click Change to proceed. To go back to using a dynamic format string, we have to provide the DAX expression again.

Format Another Column

Let’s format the Literacy Rate (%) column. But we can see the dynamic format option is not available for columns, it is only available for measure.

Let’s create a measure for this and use it in our report.

DAX

The above measure is added to the table visual as shown in the image below:

Now switch the format to Dynamic and specify the formatting string "#.##;-#.00;0" to it, as shown in the image below:

General Structure of Format Strings <PositiveFormat>;<NegativeFormat>;<ZeroFormat>

Explanation of format string "#.##;-#.00;0"

Number Format Symbols

• #: Means optional digit. Does not show extra zeroes if the digit is missing.
• 0: Means required digit. Shows 0 if there's no digit in that place.
• ,: Adds a thousand separator. It is used for grouping every 3 digits.
• .: Defines decimal precision. Separates whole and decimal parts.

Note: The format string for the Percentage is "0.00%;-0.00%;0.00%".