What is dynamic format in Power BI?
With dynamic format strings for measures, you can determine how measures appear in visuals by conditionally applying a format string with a separate DAX expression.
Dynamic format strings overcome an inherent disadvantage of using the FORMAT function.
Note: Export the measure to Excel using the Dynamic Format string, while preserving its original data type.
The FORMAT function converts the numeric data types and returned as a string, which may not work with visuals that require numeric values, like charts. When using dynamic format strings, the measure keeps its data type and isn't forced to change to a string data type. This applies different format strings to the measure depending on the context.
Create dynamic format strings
To create a dynamic format string
In the Data pane, select the measure for which you want to specify a dynamic format string.
In the Measure tools ribbon > Formatting section > Format listbox, select Dynamic. A new listbox with Format already selected appears to the left of the DAX formula bar. This drop down is how you can switch between the static measure DAX expression and the dynamic format string DAX expression. Whatever the static format string was in use before switching to Dynamic is prepopulated as a string in the DAX formula bar.
Overwrite the string with a DAX expression that outputs the correct format string for your measure. For example, the following expression changes the Format for the Dynamic measure AC vs PL % 2022_Dynamic Measure to Percent:
Verify your dynamic format string works in a visual.
Excel Export:
Frequently Asked Questions:
1. When to use Dynamic Measure format?
Ans. It is primarily useful when we need to modify the measure's format at runtime without using the PowerBI Format function, as the latter converts the measure to text.
2. Does the excel export will retain the data type of the measure?
Ans. Yes, excel export will preserve the data type of the measure when it is set to dynamic format.
3. Will dynamic format work with Performance mode?
Ans. Yes, dynamic formatting in Inforiver will function when set to Performance mode.
Note: By default, the dynamic formatted measure is set to Native measure.
