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.