Forum Discussion
Dynamic format seems to add additional "-" for negative values
I have a very simple measure
Avg Temp = AVERAGE('factWeather'[Temperature])and am building a visual where this measure shall always be displayed with "°C" as its unit of measurement and in German number formatting. I am using the dynamic format option for this:
"'" & FORMAT(SELECTEDMEASURE(), "0.0°C", "de-de") & "'"Unfortunately this results in negative values to be shown as "--1.0°C".
Using
"0.0°C"works correctly but is not using the German number formatting on non-German desktops.
I was investigating further and it seems like there is an issue of a "-" being added for negative values to any string returned by the dynamic format:
"'" & IF(SELECTEDMEASURE()>0, "+", "-") & "'"This shows "+" for positive and "--" for negative values:
Web search and forum search did not provide any meaningful results or I might have missed them. I am working with the latest PBI Desktop version.
What am I missing here and how can I solve this?
Hi _steve_,
The behavior you're experiencing is expected due to current limitations. While FORMAT() lets you specify a locale such as "de-DE", it returns the value as text. On the other hand, dynamic format strings maintain the numeric type but only accept format patterns. As noted in the documentation, the locale is set by the model or user context, not by DAX expressions. Therefore, it's not possible to combine both requirements in one method. The recommended solution is to set the model or report locale to German and u
se a format string like 0.0°C;(0.0°C);0.0°C.
Use Locale Values in Multiple-Language Power BI Reports - Power BI | Microsoft Learn
FORMAT function (DAX) - DAX | Microsoft Learn
Create Dynamic Format Strings for Measures in Power BI Desktop - Power BI | Microsoft Learn
Thank you.
6 Replies
- Kedar_Pande
Super User
Use "0.0°C;0.0°C" in your dynamic format instead of "0.0°C".
The format string positive;negative gives you control over negative values separately. First part for positive (including zero), second for negatives. Fixes the double dash issue cleanly.
Your full format: "'" & FORMAT(SELECTEDMEASURE(), "0.0°C;0.0°C", "de-de") & "'"
If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pandeThank you Kedar_Pande . This is what I also tried and it makes no sense since FORMAT() is supposed to work like the Excel custom number format. Trouble is, if I would like to have negative numbers in "( )" instead and I use the usual solution
"'" & FORMAT(SELECTEDMEASURE(), "0.0°C;(0.0°C)", "de-de") & "'"I get "-(1,0°C)" as a result. Hence my question here, if there is anything I am missing.
Also, if you look closely at the second example and the screenshot I posted, there is no "FORMAT()" involved, just an IF statement which should either show "+" or "-" and it shows "+" and "--".
It seems like there is more to it and PBI is adding a "-" to the string whenever the value is negative.
v-saisrao-msftCommunity Support
Hi _steve_,
The issue arises because the FORMAT() function outputs a text value, even when used with numeric data. According to Microsoft documentation, this can affect visuals since the measure is no longer treated as a numeric type. Dynamic format strings address this by keeping the numeric data type and applying formatting without converting the value to text. So, instead of using FORMAT(), you should use a format string (like 0.0°C;(0.0°C);0.0°C) to ensure the measure stays numeric and displays correctly in visuals.
Create Dynamic Format Strings for Measures in Power BI Desktop - Power BI | Microsoft Learn
Thank you.
