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.
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](https://datasavvy.files.wordpress.com/2023/04/image-1.png)
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.
A new drop-down box will appear next to the formula bar.
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%",
"#,###")](https://datasavvy.files.wordpress.com/2023/04/image-4.png)
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 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.
One Response