Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I have a problem with Format function when it comes to regional settings. My regional setting is Dutch (Netherlands) for current file, so thousand separator is dot, which is okay when I display it as a numer:
The problem is when I display it as string with help of a Format function which stands for:
Which is wrong because I need to display the number with dot as thousand separator not comma.
Can someone help? Maybe I use Format type "0,0" in a wrong way?
Solved! Go to Solution.
Hi @Anonymous,
It seems like the default output type does not suitable for your requirement, you can try to use custom format strings with 'common' format and add locale option to change the return results. (notice: the comma and dot characters have special definitions in the custom string so you can't directly replace the thousand and decimal separators to achieve your requirements)
Measure =
FORMAT ( SUM ( 'Table'[Amount] ), "#,##0", "nl_NL" )
Regards,
Xiaoxin Sheng
HI @Anonymous,
It seems like the power bi service does not support some detailed locale. After I change locale code to 'nl' (Dutch locale code) the visual works again on power bi service.
Measure =
FORMAT ( SUM ( 'Table'[Amount] ), "#,##0", "nl" )
Regards,
Xiaoxin Sheng
@Anonymous , In the column and measure tools you have a thousand separators, Try that.
Also use you regional separator and check -as setting is regional
Hi
but unfortunately it is a text so i dont have that option with separators:
HI @Anonymous,
In my opinion, using the format function should not be a good choice. This function will force convert the result to text type which will ignore the aggregation features.
I'd like to suggest you check and modify the system region 'formats' settings or use custom format string on your field to change the display value formats.
Solved: Re: Decimal separator : Comma / Thousand separato... - Microsoft Power BI Community
Regards,
Xiaoxin Sheng
HI @v-shex-msft
unfortunately I have to do the formatting this way because I need to implement the value as text in shape. But I had the idea that I could use the SUBSTITUTE function for the text and thus replace the comma for the dot.
HI @Anonymous,
Perhaps you can take a look at the following link about format function usage if helps:
FORMAT function (DAX) - DAX | Microsoft Docs
Sample: format with locale option.
Measure =
FORMAT ( SUM ( 'Table'[Amount] ), "Standard", "nl_NL" )
List of Country Language Locale Code - saimana.com
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Thank you very much. the measure written above works, but there is one problem that it displays a decimal value:
and I need a view:
So how can I change the "Standard" field?
Hi @Anonymous,
It seems like the default output type does not suitable for your requirement, you can try to use custom format strings with 'common' format and add locale option to change the return results. (notice: the comma and dot characters have special definitions in the custom string so you can't directly replace the thousand and decimal separators to achieve your requirements)
Measure =
FORMAT ( SUM ( 'Table'[Amount] ), "#,##0", "nl_NL" )
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
It works in Power BI Desktop, but when I publish it into Power BI Server I can't see visuals:
And there is an error:
What should I do?
HI @Anonymous,
It seems like the power bi service does not support some detailed locale. After I change locale code to 'nl' (Dutch locale code) the visual works again on power bi service.
Measure =
FORMAT ( SUM ( 'Table'[Amount] ), "#,##0", "nl" )
Regards,
Xiaoxin Sheng