Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Renaud
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
v-yuezhe-msft
Employee
Employee

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
v-yuezhe-msft
Employee
Employee

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.

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).

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.