How to use the new dynamic format strings for measures in Power BI

The April 2023 release of Power BI desktop introduced a new preview feature called dynamic format strings for measures. This allows us to return values with different formats from the same measure. Previously, we needed to create calculation groups (usually by using Tabular Editor) to accomplish this. But now it is built in to Power BI Desktop.

This is great when you are converting to different currencies or switching measures in a report page. Let’s say you have a measure that returns different values based upon a selection in another visual on the page. For instance, I created a report that allows a consumer to choose a metric to be shown across multiple visuals. Depending on the selection, I may be returning an integer, a percentage, or a currency amount. If you can use parameters to switch measures, Power BI takes care of the measure formats for you. But if you had to write your own measure to handle more complex logic, you must handle the measure formatting on your own. I did this in the below report for Workout Wednesday 2023 Week 16 because I wanted a measure that consistently filtered to only the top N products that could be used in multiple visuals on the page and didn’t unnecessarily repeat DAX logic.

A Power BI report with slicers to select the top N products and a metric. The four charts switch values based upon the slicer selections.

The measure used in my visuals was called Value.

The formula bar in Power BI Desktop showing the measure definition for a measure called Value.
Value = 
VAR SelectedMetric =
    SelectedValue ( 'Metric'[Metric] )
VAR NumProducts = [Number of Products Value]
VAR TopNMetric = Calculate(
    [Measure Switcher],
    KEEPFILTERS(TOPN(NumProducts, ALLSELECTED(Product[Product]), [Measure Switcher])))
RETURN
    TopNMetric

In PBI Desktop version 2.116.622.0 or later, there is an option set the measure format string.

As of April 2023, you need to enable the preview feature. Go to File -> Options and Settings – > Options -> Global -> Preview Features. Enable the dynamic format strings for measures option. You will need to restart PBI Desktop for this to take effect.

Next, open your report, select your measure, and change the format to Dynamic.

The format selection drop-down now has an option labeled Dynamic at the bottom.

A new drop-down box will appear next to the formula bar.

The new drop-down next to the formula bar where users can choose Measure or Format.

By default, Measure will be selected and the measure definition will be shown. But you can now change the drop-down to Format to see the format expression. The format expression is written in DAX.

In the format expression below, if the selected metric is “# of Customers”, I formatted it as an integer with no thousands separator. If the selected metric is “Gross Margin %”, I formatted it as a percentage with two digits after the decimal. Otherwise, I formatted the metric as an integer with a thousands separator.

The formula bar showing the expression for the format expression. 
SWITCH(SELECTEDVALUE(Metric[Metric]),"# of Customers", "#",
"Gross Margin %","0.00%",
"#,###")

The end result is that any axes, values, data labels, and tooltips are formatted according to my expression, while maintaining the numeric data type.

A Power BI report with slicers to select the top N products and a metric. The four charts switch values based upon the slicer selections. The Gross Margin % measure is selected, and all axes and values show numbers formatted as percentages.

A few things to note

This dynamic measure format string is scoped to the particular measure. It is a common point of frustration when people learn calculation groups to accomplish a similar goal that calculation groups are scoped to the model and require logic that limits which measures they are applied to.

The SELECTEDMEASURE() function, which is commonly used in calculation items, can be used in the dynamic measure format string to reference the measure, but you can also just use the measure name in brackets.

There are some limitations at the moment that limit use with report measures and DirectQuery for Analysis Services.

If you need more practice or would like an example involving currency conversion, you’ll find a tutorial in the Microsoft documentation.

Share

One Response

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?