Power BI Blog: Dynamic Format Strings for Measures

Welcome back to this week’s edition of the Power BI blog series.  This week, we look at the new dynamic format strings for measures.

Ever wanted to format a measure differently based upon a slicer selection or some other conditional way?  Well now you can.  This month’s update begins with the public Preview of dynamic format strings for measures.

In a measure, you define the measure itself with a DAX expression, but until now you could only provide that measure a static format string such as Currency, Whole number, Decimal, etc. or a type in the static format string, such as “dd MMM yyyy” for “09 Mar 2023”.  With dynamic format strings (it doesn’t sound grammatically correct to me – I hope they aren’t “very hidden”!), you can create that format string also using a DAX expression.  This provides you with the flexibility to adjust the format string to a variety of contexts within a report.  

For example, a common scenario for this is currency conversion.  If you have the currency format strings in your Currency table, you can define a DAX expression to use it.  To add a dynamic format string to a measure, click the measure in the Data pane, then in the Measure tools ribbon Format dropdown choose ‘Dynamic’.

A new dropdown will appear to the left of the DAX formula bar, and it will be on ‘Format’.  By default, whatever static format string corresponds to the previous Format dropdown will be pre-populated to get you started, but you can also delete it and use whatever DAX expression you want for your dynamic format string.  In this example, it’s looking up the string from ‘Currency’ [Currency Format], and if that is ambiguous, then using

If you want to get back to your measure DAX expression, you can change that left dropdown to ‘Measure’.

Finally, if you want to remove the dynamic format string, go back to the Format dropdown, and choose any of the other options available.  A warning dialog will appear as you will not be able to undo this action – so be careful!

Dynamic format strings are not new, and those of you who are familiar with calculation groups in SQL Server Analysis Services, Azure Analysis Services, and / or Power BI using external tools may know calculation items (supported in some tabular models, these can significantly reduce the number of redundant measures by grouping common measure expressions as calculation items)  already have dynamic format strings.  These same dynamic format string DAX patterns can now be utilized in individual measures in Power BI too.

In the meantime, please remember we offer training in Power BI which you can find out more about here.  If you wish to catch up on past articles, you can find all of our past Power BI blogs here.