DAX Format Function Localization error

[C4YNelis]

I'm trying to format a measure such as below (the outcome is encircled in red). The problem with measures such as these, is that they have a possible text outcome (not any). Power BI therefore considers the date not to be whole numbers, but text. No problem, since that is why we have a text function called "Format", right? (I know there is a sorting issue with the Format function, however, this is not my concern right now, since I am only using this in a card visual at the moment). My problem is that I can't seem to get the thousand separator to become a dot.

My localization is Dutch, my display language is English. This means I normally see all decimals as commas and all thousand separators as dots. For regular Whole Number data columns, this happens just fine. The same goes for "normal" measures performing some type of aggregation (sum, count, etc), as long as they always result in a number and are thus formatted as whole numbers by Power BI (encircled in green).

 

totals measure = if('someTable'[someCount_Measure]=0;"NA";format('someTable'[someCount_Measure];"#,##0"))

According to the information I found, I need to enter a comma as a thousands separator and Power BI would need to use my localization settings to correct this to a dot. However, despite my regional settings being Dutch (both Windows 10 and Power BI), it keeps giving me a comma. When I try a Dot instead, I get a dot like expected, which behaves as the decimal sign.

Now of course I can just substitute the comma for a dot with the substitute function, which I have done, but this feels very much like a bug to me. Also, this easily becomes very tricky when you actually do need the decimals. The other thing is that this will result in incorrect data when someone with their regional settings set to English will open the report.

Thank you for your consideration,

cheers,

Niels

p.s. I have seen many posts about format, I could not find one that covers this exact issue. If their is one, I'm sorry to have missed it. Feel free to remove my post in that case.

[v-qiuyu-msft]

Hi @C4YNelis,

Please update the Power BI desktop to the latest version 2.57.5068.501. Make sure the region number thousands separator is dot. If you change the region number setting after open the report, please restart the Power BI desktop apply the change. 

Best Regards,
Qiuyun Yu 

[C4YNelis]

Hi @v-qiuyu-msft,

thank you (again) for your support! I already have that exact same version (Version: 2.57.5068.501 64-bit (april 2018)) and indeed the region number thousands separator is a dot (just to be sure, I have double checked both the regional settings in Power BI (which are Dutch) and the regional settings in Windows (also Dutch). The settings are all default and specifically the thousand separator is a dot. I'm pretty sure this is not cause of the issue.

Just to emphasize, the problem is not the "regular" formatting of columns, that works just fine. The problem is specific only to the 'format' function in DAX.

Thank you again.

[C4YNelis]

To add a screenshot:

screenshot: Regional settings

Cheers,

Niels

[v-qiuyu-msft]

Hi @C4YNelis,

I have reported this issue internally: CRI 66298382. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @C4YNelis,

I got below information below: 

The DAX format function follows the locale of the model. There are 2 ways this can be controlled, depending how the user installs PowerBI Desktop.

1. If they are getting it from Windows Store, PBI Desktop will have an additional set of Regional Settings, one of which is "Model Language". This allows the user to chose the specific language they want DAX to follow. This only applies for new models, existing ones cannot be changed.

2. If the user is getting the installer from the PowerBI web page, that installation has just one language available and there is no setting for changing the model language. All the models created with that installation will have the installation language set by default. For example if they download the English PBI Desktop all models created will be in English, if the download the Dutch PBI Desktop, all the models created will be in Dutch.

In your scenario, do you install Power BI desktop from Windows Store or Power BI web page? 

Best Regards,
Qiuyun Yu 

[C4YNelis]

Dear @v-qiuyu-msft,

thank you for your feedback.

I have downloaded the English installer version from the website.

If I understand correctly, whenever someone with a non-English locale in Windows will download and install the English version of Power BI (which would seem to me to be a very common scenario, especially in international companies) there will be a difference between any DAX model locale generated by Power BI and the windows Regional locale, resulting in conflicts such as these?

To add to this: based on your words that "existing ones cannot be changed.", this also means that there is no way to correct this afterwards, other than changing your Windows regional settings to "English" to align your report and adjust accordingly? This is not really a viable option for me unfortunately. Basically I need to install the Dutch version next time? Is there any change this behavior of the DAX Model will be changed in the (near) future?

cheers,

Niels

[v-qiuyu-msft]

Hi @C4YNelis,

I have delivered your concern internally, will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @C4YNelis,

Please see below information I got: 

This is the current state of the product and I am not aware of any change coming up in the near future. I advise the customer to post their needs in the ideas section of the PowerBI website in order to get PMs to prioritize this work. https://ideas.powerbi.com/forums/265200-power-bi-ideas

Best Regards,
Qiuyun Yu