Making Dynamic Format String More Dynamic for End Users, in Power BI

In this writing, I want to share how I learned to make dynamic format strings more dynamic and flexible for Power BI end users. This exploration started with a challenge: displaying large ranges of sales figures across different countries in a table visualization. The goal was to make these numbers both compact and easy to interpret at a glance.

Current Situation: The Challenge

In my report, sales data ranged widely across different countries. This made it difficult to draw quick insights because:

• Visual Imbalance: Larger numbers took up more space, making the table look cluttered and unaligned.

• Readability vs. Usability: Striking a balance between compact formatting and effective comparison was challenging

First Solution: Using Standard Format String Table

My initial approach was to implement Dynamic Format Strings using a predefined set of format options from a table similar to the one below (table name: standard_format_string). While Power BI developers can create more complex tables based on specific needs, this simplified version serves well for demonstration purposes.

Format String expression:

How the table visualization looks like:

• Pros: The results were displayed in a compact, neat format, making the large numbers more readable (e.g., 964,532,981 displayed as 965M).

• Cons: The compact format made it harder to distinguish differences between numbers at a glance. (e.g., 468K vs. 8.77M).

Second Solution: Conditional Formatting with Data Bars

To enhance visual differentiation, I tried to add conditional formatting with data bars:

• Pros: The bars helped highlight significant values immediately. Big numbers stood out clearly.

• Cons: Smaller values, unfortunately, were represented by nearly invisible bars, making them hard to discern.

Final Solution: Giving Users Control with Format Selection

To tackle these challenges, I introduced a format selection table, designed as a disconnected, standalone table in the data model (table name: style).

In this demo, I added the 'style'[style] column to the Filter Pane under the "Filters on this visual" level. However, it can also be applied at the "Filters on this page" or "Filters on all pages" level, depending on the desired scope.

This enabled users to dynamically choose how they wanted the numbers to be displayed—either in the current format with data bars or using the compact format from the first solution with data bars.

Format String Expression:

VAR _measure = [Sales:]
VAR _formatstring =
    MAXX (
       FILTER (
            standard_format_string,
            standard_format_string[category]
                = SWITCH (
                    SELECTEDVALUE ( style[style] ),
                    "style01", "number",
                    "style02", "number00"
                )
                && _measure >= standard_format_string[min]
                && _measure < standard_format_string[max]
        ),
        standard_format_string[format_string]
    )
RETURN
    _formatstring

How the visuals look like:

This approach gives control back to the user, providing flexibility and enhancing the report’s interactivity. Users could explore the data based on their preferences, making it more engaging and insightful.

Summary

Each solution had its merits and challenges, but the ultimate takeaway was that user-driven flexibility is key. By integrating a slicer (in this demo, I used Filter pane) for format selection, I was able to strike a balance between readability, visual impact, and customization.

In this demo, I focused on a simplified version of the standard_format_string table to demonstrate key concepts. However, this table can be expanded with additional categories and formatting options to provide even greater flexibility for end users. By incorporating more conditions and custom formats, you can tailor the experience to meet diverse needs, making reports even more dynamic and user-friendly. This opens up possibilities for enhancing interactivity and personalization in Power BI. Experimenting with more categories will allow users to switch between a wider variety of formats, ensuring they have complete control over how data is presented.

I hope this helps having more fun implementing dynamic format strings in various ways, pushing the limits of what Power BI can achieve. Happy POWERBIing!