Dynamic format strings

Dynamic format strings for measures enable advanced customization of how numeric data is displayed in visuals, offering a powerful alternative to the traditional FORMAT function. With dynamic format strings, users can conditionally apply different format strings to a measure based on the context while keeping the data type intact, unlike FORMAT, which converts numeric values to strings. This, in particular, isn’t acceptable for Zebra BI visuals, which require numeric values to calculate variances on the fly.

This article will explore the benefits of dynamic format strings, how to implement it, and practical applications.

Creating Dynamic Format Strings for Measures#

To implement dynamic format strings in Power BI, follow these steps:

• Go to the Data pane, and choose the measure you want to apply a dynamic format string to

• Go to the Measure Tools ribbon and find the Formatting section. In the Format dropdown, select Dynamic. A new dropdown will appear next to the DAX bar, allowing you to switch between the static DAX expression and the DAX expression used for the dynamic format string.

• The DAX bar will populate the static format string in advance. You can overwrite this with a DAX expression that applies the correct format string based on context. One key benefit is its ability to adapt its value in response to the filter context or the calculated measure, thanks to its dynamic nature.

Enable reading format from the model in Zebra BI visuals#

By default, Zebra BI visuals have an Auto setup for units, which dynamically adjusts the display of numerical values based on the size of the numbers. If you want to change the logic and apply a format you’ve set in the model, go to Settings > Data labels > Units and select Power BI.

You can learn more about this option through several demonstrations at this link.

Practical examples#

Example #1#

Imagine you want a dynamic measure where you can determine your own thresholds for changing units. This can be easily achieved with the dynamic format strings. Let’s take a look at the example below:

We have a table of countries whose revenues massively vary. When no units are applied in a table, we can quickly get lost in numbers, and they take up too much space.

So, let’s simplify numbers by transforming them into different units based on their value. Additionally, we can also include the number of decimal places we want to display for each unit. Below is the DAX code used to establish thresholds for displaying various units, along with a format adjustment to accommodate a KPI that requires an entirely unrelated unit type—percentages.

VAR format_ =
    SWITCH (
        TRUE (),
        SELECTEDMEASURE () >= 1000000, "#,0,,.00M",
        SELECTEDMEASURE () >= 1000, "#,0,.0K",
        SELECTEDMEASURE () >= 1, "#."
    )
RETURN
    IF ( SELECTEDVALUE ( KPIs[KPI_ID] ) = 4, "0.0%", format_)

After applying this DAX to the Format section of a measure, the result in a table changes from the one above to the one below, where each threshold also reflects a different number of decimal places:

Selecting a KPI with percentages also returns a correct format:

Example #2#

Now, we can take a look at another example. The report is being utilized in several countries, and every country needs its own currency displayed. Therefore, we need to modify the measure to return a different currency for each country. We also need to set up a conversion for every currency to get accurate values.

First, create a new Table using the Enter data option. Add countries and currencies, and at the end, add an exchange rate column for each currency, which will be used in a measure later.

You can copy a sample data below

Country	Currency	Exchange rate
United States	US$#,0.00	1.00
United Kingdom	£ #,0	0.81
Germany	€ #,0.00	0.95
Canada	C$#,0.00	1.30

Then, prepare a new measure that will dynamically switch between currencies and also change return amounts with the correct exchange rates. Take the existing AC measure for reference and multiply it with the Exchange rate:

AC Currency = [AC]* MIN('Currency'[Exchange rate])

Then change the Format to Dynamic and navigate to the dropdown next to DAX formula bar, switch to Format, and type in the following expression:

SELECTEDVALUE('Currency'[Currency])

This will indicate an appropriate currency when the country is selected in the slicer/filter.

You can upgrade the existing measure by incorporating the dynamic thresholds mentioned above. Instead of defining the format within the currency table, as shown below, you import a table containing only currency symbols and combine that field with the dynamic format.

Now you can change the DAX behind the format of the measure:

You can copy the code below:

VAR cur_ =
    SELECTEDVALUE ( 'New Currency'[Currency] )
VAR base_ =
    SWITCH (
        TRUE (),
        SELECTEDMEASURE () >= 1000000, "#,0,,.00M",
        SELECTEDMEASURE () >= 1000, "#,0,.0K",
        SELECTEDMEASURE () >= 1, "#."
    )
VAR format_ =
    IF ( SELECTEDVALUE ( KPIs[KPI_ID] ) = 4, "0.0%", base_ )
RETURN
    cur_ & " " & format_

This setup consolidates the format, automatically adjusting units (e.g., thousands, millions, none) based on the value, and includes currency selection:

Summary#

In conclusion, dynamic format strings in Power BI provide a flexible and efficient way to control the display of numerical data without altering its underlying type. By leveraging DAX expressions, users can adapt format strings based on various contexts, including specific thresholds, currency requirements, and measurement types. This approach offers an alternative to the traditional FORMAT function, maintaining the numeric integrity needed for advanced visuals like Zebra BI, which rely on accurate calculations for features like variance analysis. Through the examples provided, dynamic format strings are shown to enhance clarity in reports and streamline data presentation, making them a valuable tool for creating contextual visualizations.