cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate IV
Advocate IV

Format() not using the regional settings

Hello,

 

I am having issues when formating numbers in strings using the format() function.

 

My local regional settings on power BI, as well as my regional settings on Windows, are set to English (Singapore), which normally use "." as decimal separator and "," as thousand separator.

This works fine when I shows measures in visuals such as tables, cards, etc.

 

However, when creating a text measure (see below), using a format(..... , "0,0") function, the thousand separators becomes space (which is used in the french format). I don't see why format() shouldn't behave the same way as a measure of type number.
Can someone help?

 

 

format issue.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft
Microsoft

Hi @Renaud,

I am not able to reproduce your issue in the latest version of Power BI Desktop (2.45.4704.722), please check the region settings in my environment.
1.PNG

Do you use same version of Power BI Desktop as mine? And how about using the following formula to create your measure?

measure= FORMAT([qty],"0%") &" of " & FORMAT([INITIAL BUY],"#,0") & " pcs "

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

It worked well.

Please note I used WinZip and Notepad++ as editor. I added the modified file to the existing ZIP (renamed from PBIT) instead of creating a new one from scratch.

You can see the result here (dropbox).

View solution in original post

9 REPLIES 9
Microsoft
Microsoft

Hi @Renaud,

I am not able to reproduce your issue in the latest version of Power BI Desktop (2.45.4704.722), please check the region settings in my environment.
1.PNG

Do you use same version of Power BI Desktop as mine? And how about using the following formula to create your measure?

measure= FORMAT([qty],"0%") &" of " & FORMAT([INITIAL BUY],"#,0") & " pcs "

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi Lydia,

 

I've tried reproducing the issue also on my side from a new PBI Desktop file, and there was no issue anymore...

Not sure what happens with my previous file. The only explanation i have is that i had started that file with a French locale settings, later changed to English.

Since new files seems to work, I think we can close the issue 😉 Thanks for your quick reply!

I wrote an article explaining how to change the culture settings that is inherited from the operating system current culture when you create a Power BI Desktop file and that you cannot change later (in Power BI Desktop UI).

 

http://www.sqlbi.com/articles/changing-the-culture-of-a-power-bi-desktop-file/

 

I hope it helps.

 

Marco Russo - SQLBI

Hi @marcorusso

 

that's great. Unfortunately, after the modification i couldn't reopen the file as it says it is corrupt.

By the way I couldn't find any other mention of culture settings in the DataModelSchema file, just the one at the top.

I could only find this at the beginning:

 

{
  "name": "d8357587-d578-4fef-992d-15d4d983939d",
  "compatibilityLevel": 1400,
  "createdTimestamp": "2017-04-14T11:00:28.296667+08:00",
  "lastUpdate": "2017-08-18T17:14:54.16+08:00",
  "lastSchemaUpdate": "2017-04-14T11:00:28.296667+08:00",
  "lastProcessed": "2017-07-14T09:43:20.556667+08:00",
  "model": {
    "culture": "fr-FR",
    "modifiedTime": "2017-04-14T03:00:28.396667",
    "structureModifiedTime": "2017-07-14T01:42:06.956667",
    "dataSources": [
      {
...

I've only modified "fr-FR" to "en-US".

 

To modify i had to unzip, modify the file, and re-zip. Even without modification it seems that the unzip/re-zip is the reason why the file gets corrupted

Can you repro that with a file that you can send me?

 

Marco Russo - SQLBI

HI @marcorusso

 

thanks for taking time to help on this. Here you go, i stripped down the file from all data/models/report, with just one test measure that is still displayed in fr-FR.

 

Test file (dropbox)

It worked well.

Please note I used WinZip and Notepad++ as editor. I added the modified file to the existing ZIP (renamed from PBIT) instead of creating a new one from scratch.

You can see the result here (dropbox).

View solution in original post

Thanks Marco. Unfortunately, I am unable to reproduce what you did, even with winzip and notepad++.

There is probably something else that i'm doing wrong.

 

Maybe other users can try on their own files and feedback if they have any issues

Hi Marco,

 

I have retried on a recent PBI desktop file and it worked. I'm not sure why it didn't work previously.

I used the Windows explorer zip utility and notepad++ (didn't work with the windows notepad).

Also, previously I somehow couldn't find the second part that u mention (the "cultures" section), i have found it now.

 

thanks for all your insight

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors