DAX FORMAT not using correct thousand separator

[Piloos]

Hi,

I am facing an issue where the DAX FORMAT formula is not honoring the locale of the user.  For example, in Dutch, the thousand separator is typically a dot (.) while the decimal separator is a comma (,).

If you add show a numerical measure on a card and you enable the thousand separator it correctly honors your locale.  As you can see in the screenshot, the thousand separator is a dot (.).  So far so good!

If you then create a more complex measure where you use the DAX FORMAT formula to convert a number to a string indicating that the string should contain thousand separators, Power BI does not use the correct thousand separator.  So you have a discrepancy in behavior.

This is also directly in contradiction with the documentation (https://docs.microsoft.com/en-us/dax/format-function-dax😞

Thousand separator. In some locales, a period is used as a thousand separator. The thousand separator separates thousands from hundreds within a number that has four or more places to the left of the decimal separator. ...  The actual character used as the thousand separator in the formatted output depends on the Number Format recognized by your system.

Screenshot of the issue:

This problem has been recognized earlier:

https://community.powerbi.com/t5/Issues/DAX-Format-Function-Localization-error/idi-p/393276

https://community.powerbi.com/t5/DAX-Commands-and-Tips/issues-with-FORMAT-and-decimal-thousands-sepa...

The first thread indicates that the FORMAT function follows the locale format of the model (aka the model language).

I can confirm that this is indeed true.  If you change the model language of your PBI desktop installation, and you start a BRAND NEW report, the separators used by FORMAT follow this language.  However, this does not make sense at all.  I understand that there should be an internal format (which should not be changed), but the format displaying towards the user should follow the format of the user, just like the normal measures already do.

So to summarize:

* Discrepancy between displaying of normal measures vs. measures using the FORMAT formula.  ---> BUG

* Documentation about FORMAT formula is incorrect --> WRONG DOCUMENTATION

Please let me know if I can help solving this issue.

[v-alq-msft]

Hi, @Piloos 

I tested with sample data and failed to reproduce your scenario.  You may refer to the document to see if it helps. If you have a pro license, I'd like to suggest you create a support ticket here for further help.

Best Regards

Allan

[Piloos]

@v-alq-msft ,  thanks for taking the time to try to reproduce the scenario.  I assume that you see everything correctly formatted.  May I ask you which Windows locale (language) you use?  I can send you an example with a different model language to help you reproduce the issue.

Best regards,

Lode

[v-alq-msft]

Hi, @Piloos 

The Windows locale I used is English. Please mask sensitive data before uploading. Thanks.

Best Regards

Allan

[Piloos]

Hi @v-alq-msft , I was about the create a PBIX with Dutch model language, but then I realised that you might have this model language available (do you? check in options --> global settings -> regional settings).

Instead I have created an example with the default English model language.  You can just reproduce this issue when you upload this report to the service and change you browser language to Dutch.

You can download the PBIX to reproduce here:

https://bizzcontrol-public.s3.eu-central-1.amazonaws.com/reproduction.pbix

This is a screenshot of the issue:

 

[Piloos]

In the meantime, the behavior was confirmed by the Power BI support team but the product team confirmed that this is by design.

When we use the FORMATE function in DAX it will consider formatting a String, and this format will apply on the Analysis server side. 
so when you change the settings in service these settings will not apply for visual level using performance analyzer in desktop for both visuals DAX Query is not same and it will not Run as same on service due to FORMATE function in DAX which why you will not see any changes in visual power bi service or for DAX format visual. "

Currently, this is by design

[Joris_NL]

Had the same issue, but solved it.

By multiplying the DAX FORMAT formula by 1, it resets the use of points and comma's back to regional settings. Afterwards, use the the standard formatting tab to get what you want.

Example:

MeasureNotWorking = format([VarianceAbsolute]);"€0.00")

which for example results in €12,345.66

Which is wrong in this case (Dutch)

MeasureWorks = format([VarianceAbsolute]);"0.00")*1

Then set the format tab to 'currency' with two digits. Results in: €12.345,66

Right?