We have created a model in Power BI Desktop and have had the need to create a complex DAX query that is using the FORMAT function to display different types of values in the same measure.
After using the FORMAT we have noticed that the Thousand and Decimal Separators are switched.
We are based in Portugal and the "." is used for Thousands and "," is used for decimals.
In an attempt to find the root cause we have created a copy of the pbix and have removed all datasources and just created the following measures:
Measure 1 = 10000,23;
Measure 2 = FORMAT([Measure 1];"Currency");
Measure 1 is formated as Currency (Done in Measure Tools > Formating) and shows € 10.00,23
Measure 2 shows $ 10,000.23. It should show € 10.000,23
We have changed Regional Settings (both global and current file) in the file and Measure 2 continues the same.
We also changed the DAX separator section to Use localized DAX separators.
We've also created a completely new file with the same Measures and everything works fine which is even stranger. The files are identical in every way we checked.
Could you please help in understanding what could the problem and a work around?
Recreating the whole file is not good option since everything has already been tested and this is the only thing that needs to be corrected.
The model language only applies when a report is first created, and can't be changed on exising reports.
That also happen in Spain, when I import data from Oracle field number type. My solution was import first as string and then convert to Decimal Number.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.