\<< [[§§ Issues, solutions and workarounds for Power BI Development]] --- # TL;DR Power BI has a native feature to allow for dynamic format for measures (for example, changing the scale for number quantities from Millions to Thousands or else based on the scale of the number displayed). This feature however doesn't work when measures are located in Shared Semantic Models. The workaround for that is to either use dynamic format in the visual level or to use FORMAT() and adjust the measure output directly. --- # Description Standard dynamic format strings feature: ![[publish-asset 20250110213637.png]] It seems to be a known limitation for this feature when connecting to live data models and using measures created in the report, it works [just for measures created on the semantic model](https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings#:~:text=Dynamic%20format%20strings%20for%20measures%20are%20only%20for%20model%20measures.%20Report%20measures%20that%20can%20be%20added%20to%20a%20live%20connect%20report%20can%27t%20have%20dynamic%20format%20strings%20for%20measures) If creating measures on the model is not possible or not wanted, the following alternative can be used. # Solution 1 - Use conditional format for data labels number format when available Some charts offer the option to have custom format for data labels. If the option is available for the selected visual, you can use a measure similar to the example below to dynamically change the format of numbers depending on the scale: ``` Dynamic Format Measure = VAR _measure = [measure] RETURN SWITCH( TRUE(), _measure > 1000, "0", _measure > 1000000, "#.0k", _measure > 1000000000, "#.0M", "#.0B" ) ``` ## Limitations - Not possible for all visual types - Standard tooltips won't contain the number format for the label - Seems to be static for different slicer selections # Solution 2 - Use measure with FORMAT() in the data label field Similar to the solution 1, however, instead of having a measure returning a format string to format a field with data type number, it uses a measure that returns the number already formatted as text. ``` Dynamic Format Measure = VAR _measure = [measure] RETURN SWITCH( TRUE(), _measure > 1000, FORMAT(_measure, "0"), _measure > 1000000, FORMAT(_measure, "#.0k"), _measure > 1000000000, FORMAT(_measure, "#.0M"), FORMAT(_measure, "#.0B") ) ``` # References - https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings