Want to format a measure based on a slicer selection, the measure value, or another conditional way? Now you can! With dynamic format strings for measures a DAX expression can now be used to determine what format string a measure will use.
These dynamic format strings for measures are the same dynamic format strings already available in calculation groups! If you are familiar with these in calculation items, the DAX patterns you used there are applicable here to individual measures. Learn more about calculation groups at https://aka.ms/calculationgroups.
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, you can now use dynamic format strings for measures to get around this drawback!
Dynamic format strings for measures is in public preview. To use this feature first go to File > Options and settings > Options > Preview features and check the box next to Dynamic format strings for measures.
To add a dynamic format string to a measure,
- Click on the measure in the Data pane.
Graphical_user_interface_Description_automatically_generated
- In Measure tools ribbon, expand the Format list box.
- Then choose Dynamic.
Graphical_user_interface_Description_automatically_generated
- 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.
Graphical_user_interface_application_Word_Description_automatically_generated
- I can overwrite this pre-populated string with whatever DAX expression will output the desired format string for my measure. In this case I am looking up the appropriate currency format string from the ‘Country Currency Format Strings’ table and enter this DAX expression:
SELECTEDVALUE (
'Country Currency Format Strings'[Format],
"\$#,0.00;(\$#,0.00);\$#,0.00"
) - I click the check mark to save the dynamic format string for my measure to the model.
- Then I can see the dynamic format string working in the visual.
Graphical_user_interface_text_application_Description_automatically_generated
- After selecting the measure in the Data pane, I go to the Measure tools ribbon and expand the Format list box.
- I can pick any other format option.
Graphical_user_interface_application_Description_automatically_generated
- A dialog will appear asking if I want to proceed as there is no undo to this action. Click Change to proceed. To go back to using a dynamic format string, I will have to provide the DAX expression again.
Graphical_user_interface_text_application_Description_automatically_generated
1) Currency conversion and showing the results with the correct currency format string – A common scenario is in a report converting from one currency to another. When the value is converted, the report should show the converted currency in the appropriate format.
There are step by step instructions available at https://learn.microsoft.com/power-bi/create-reports/desktop-dynamic-format-strings#example to set up the Adventure Works 2020 PBIX file with the needed tables for this currency conversion example.
Here in my Adventure Works 2020 data model, I have the yearly conversion rates for some countries in the table ‘Yearly Average Exchange Rates’.
I also have a table with the countries I want to convert to, and their currency format strings as the 'Country Currency Format Strings' table.
To join these to my existing tables, I add relationships to the new tables.
First, I create a relationship between the ‘Country Currency Format Strings’ table and ‘Yearly Average Exchange Rates’ on the Country column. Make the relationship one to many and so that ‘Country Currency Format Strings’ filters ‘Yearly Average Exchange Rates’.
Second, I create a relationship between the ‘Date’ table and the ‘Yearly Average Exchange Rates’ table on the Year column. Make the relationship many to many and so that ‘Date’ table filters” the ‘Yearly Average Exchange Rates’ table.
With all this set up, I then create a measure to compute the exchange rate with this DAX expression:
IF (
ISBLANK (
SELECTEDVALUE ( 'Country Currency Format Strings'[Country] )
)
|| SELECTEDVALUE ( 'Country Currency Format Strings'[Country] ) = "United States",
1,
AVERAGE ( 'Yearly Average Exchange Rates'[Yearly Average Exchange Rate] )
)
And then I create the measure [Converted Sales Amount] to convert my existing [Sales Amount] measure to other currencies with this DAX expression:
Converted Sales Amount =
SUMX (
'Date',
CALCULATE (
[Sales Amount] * [Exchange Rate (Yearly Avg)]
)
)
'Country Currency Format Strings'[Format],
"\$#,0.00;(\$#,0.00);\$#,0.00"
)
Now when a country is selected in the slicer, the [Converted Sales Amount] shows not only the converted [Sales Amount] but also shows the value in the specified format. And because this is done with the dynamic format strings for measures, the underlying data type of the measure remains numeric and is usable in any visual like before.
2) User driven format strings – Different teams may want to see the report formatted in different ways for their reporting needs. Dynamic format strings for measures can be report user driven to indicate how they want to see the number formatted. And in some formatting cases, such as when abbreviating 1,000s, the dynamic format strings for measures can also conditionally format based on the measure value.
For the top slicer I create a calculated table to define the format strings in my model. In Modeling ribbon I click on New table and enter the following DAX expression:
DATATABLE (
"Select", STRING,
"Format String for $", STRING,
{
{ "in K (1,000s)", "$#,##0,.0 K" },
{ "in M (1,000s)", "$#,##0,.0 M" },
{ "💵", "$#,##0💵" },
{ "Full", "$#,##0" },
{ "Full with decimals", "$#,##0.00" }
}
)
Which looks like this in the Data view:
Now I create this measure:
Then I go to the Measure tools ribbon and choose Dynamic from the Format list box. In the DAX formula bar, I enter the following dynamic format string DAX expression:
LEFT (
SELECTEDVALUE ( 'Select format'[Select] ),
2
) = "in"
&& SELECTEDMEASURE () < 1000,
"$#,##0",
SELECTEDVALUE (
'Select format'[Format String for $],
"$#,##0.00"
)
)
I could have alternatively also written it without using SELECTEDMEASURE() using the measure name itself, [Sales Amount (Pick)], like this:
LEFT (
SELECTEDVALUE ( 'Select format'[Select] ),
2
) = "in"
&& [Sales Amount (Pick)] < 1000,
"$#,##0",
SELECTEDVALUE (
'Select format'[Format String for $],
"$#,##0.00"
)
)
With this now in place, the visuals with [Sales Amount (Pick)] will show the value in the specified format. In the cases where abbreviating to 1000s such as when using K to abbreviate, any number under 1000 will show the full value and not be abbreviated.
3) Measure driven format strings – In the previous example the measure itself was used to determine how the value would be formatted when abbreviated by 1000s. I can take this further and have the measure value fully determine the abbreviation limits and formatting. This is like the “Auto” option in display units on visuals, but now I get to define exactly how it works with my measure using dynamic format strings.
I create a new measure called [Sales Amount (Auto)] defined as:
And I add this dynamic format string expression to [Sales Amount (Auto)]:
TRUE (),
SELECTEDMEASURE () < 1000, "$#,##0",
SELECTEDMEASURE () < 1000000, "$#,##0,.0K",
"$#,##0,,.0M"
)
If I had negative values, I could include those limits, and if I had very large numbers, I could also abbreviate and format them appropriately too. Now the visuals will show this measure abbreviated and in format I have defined:
4) Locale driven currency conversion – I may know the locale of the country I am converting to, but not the exact currency format rules, or noticed it is tricky to get that format string correct for currencies that flip the . and , in their format strings. Here we can leverage the updated FORMAT function that can also take a locale argument! When used with the dynamic format strings for measures we can still keep the measure as a numeric data type and use FORMAT. The drawback to this approach is you cannot customize the currency format string for that locale further.
I create the Locale table using the Modeling ribbon’s New table and enter the following DAX expression:
DATATABLE (
"Locale", STRING,
"Country", STRING,
{
{ "en-US", "United States" },
{ "de-DE", "Euro Zone" },
{ "pt-BR", "Brazil" },
{ "fr-FR", "Euro Zone" },
{ "en-AU", "Australia" },
{ "fr-CA", "Canada" },
{ "da-DK", "Denmark" },
{ "en-SE", "Sweden" },
{ "se-SE", "Sweden" },
{ "en-CH", "Switzerland" },
{ "en-GB", "United Kingdom" },
{ "ja-JP", "Japan" }
}
)
I then create a relationship from the ‘Locale’ table to the ‘Country Currency Format Strings’ table on the Country column. This should be many to one, and cross filtering in both directions for this example. As these are small tables and not part of a complex model, I am ok with the using cross filtering in both directions here.
I create a new measure [Converted Sales Amount (Locale)] with this DAX expression:
[Converted Sales Amount]
And give [Converted Sales Amount (Locale)] measure the following dynamic format string DAX expression:
SELECTEDVALUE ( Locale[Locale] )
RETURN
"'"
& FORMAT (
SELECTEDMEASURE (),
"Currency",
_locale
) & "'"
The FORMAT function itself will output a string that already formatted the value of the measure into the appropriate currency format for the given locale. Because I want this string to be used literally, that is, I don’t want any part of it to be used like a format string, I am wrapping it in single quotes.
Now I can use this locale driven currency formatting in the visuals!
I can now compare the locale driven currency format strings with the first example where I defined the format string manually.
These four examples are just the beginning. I'm excited to see all the other creative ways you'll use dynamic format strings for measures in your reports! There are some things to keep in mind using dynamic format strings for measures.
- Visual display units: Visuals have formatting options that may impact how the format string is displayed, so if the formatting is showing unexpectedly in a visual, go to the visual Format options search for “Display units” and change it from “Auto” to “None”.
Graphical_user_interface_application_Description_automatically_generated
- The measure itself can be referenced directly in its dynamic format string for measures by using its name, for example [Measure A], or indirectly by using SELECTEDMEASURE().
- Report measures: Dynamic format strings for measures are only for model measures. Report measures which can be added to a live connect report cannot have dynamic format string for measures. Currently there is a bug where Dynamic does show in the Measure tools ribbon Format list box and when selected the Measure/Format drop down to the left of the DAX formula bar will show, but you cannot add a DAX expression. This will be fixed in a future release, and the report measures will not see the Dynamic option in the Format list box.
- DirectQuery over AS: When you click the Make changes to this model on a live connect report this will shift the connection to the DirectQuery over AS, and generally we allow you to make changes to the format strings of the remote model measures. For public preview of dynamic format strings for measures,
- Remote model measures with dynamic format strings defined will be blocked from making format string changes, to a static format string or to a different dynamic format string DAX expression.
- Remote model measures cannot be changed from a static format string to a dynamic format string DAX expression defined in the local model.
- Local model measures will also be blocked from using dynamic format strings for measures.
Try dynamic format strings for measures today and learn more at https://learn.microsoft.com/power-bi/create-reports/desktop-dynamic-format-strings.