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
autodafe
Resolver I
Resolver I

issues with FORMAT and decimal/thousands separator

I have a quite complex DAX formula that returns different values (Euros or Euros/Acres). I am using a card to display it. IN some cases I need to displat decimal, in others I don't want them to be shown.

Normally the value is displayed like "46.079,25" - with a comma for decimal and dot as thousands separator.

This is the expected format as regional settings are set on Italian both on my machine and the .pbix file.

I need to remove decimals when the measure is asked to display Euros only. So, it should be like "46.079"
-If  I use FORMAT(MyMeasure, "#,###") - which should be the correct "mask" then it displays "46,079" using the comma as thousands separator.
-If I use FORMAT(MyMeasure, "#.###") them it displays "46079.25" using the dot as decimal separator.

I need it do display "46.079"
thanks in advance!

15 REPLIES 15
Jeanxyz
Post Prodigy
Post Prodigy

I had the same issue. It has nothing to do with region setup. My region setup is English(Belgium), if I open a new Power BI report and copy the measure, the seperator is comma, but in the current file I'm working on the seperator is dot even though the measure is the same. 

 

I am experiencing very similar issue (but not exactly identicle). The issue being I am trying to use format mask below in Paginal Power BI Report Builder "$0.00,,;($0.00,,);'-'" and though the report builder does not throw any error it renders incorrectly in UI (page view/ web view) with 2 digits decimal display e.g. $.9.52 (which is what the original number is but not how expected to render with this format mask)...

 

However same report when exported to excel it renders as expected in Excel... implying this is a bug/ errage in Paginated Report Builder...

amitchandak
Super User
Super User

@autodafe , Thanks for sharing. Means the setting works, only when they are done before you start the work in a new file.

Yeah @amitchandak , changing the language of the Model AFTER you created it has apparently no effect on the current file, only on newly created files, and it is also specificed on the option.
I had probably switched to English for a PBI course I had to teach to an international audience, and then probably switched back to Italian. This might be the case, and now I guess I am stuck, unless I write something to "split" the measure every 3 digits and add a dot. I have seen something similar done here on the forums...Gosh!

@Pragati11yes, the new sample I made works correctly as you observed. This was created with the Model Language set to Italian, and the FORMAT function works correctly.

What has probably happened is: I created my original file with English Language for the Model (see above for explanation on "why") and then switched to Italian. I didn't realize it until now...
Model language is only effective at the time of model creation , and cannot be changed afterwards...
Now I will need to find a workaroudns for that 🙂

@Pragati11  actually I see that on your machine the "Euro" values is displayed correctly as "12.345" (dot for thousands) and the Euros/Acres values is "160.32" (dot for decimals instead of a comma)

The sample I made on my machine works correctly, but my original .pbix file doesn't

Untitled Project.gif

Pragati11
Super User
Super User

Hi @autodafe ,

 

There is a blog on how you can dynamically chnage the format of the data to be displayed. Check if this helps:

https://radacad.com/dynamically-change-the-format-of-values-in-power-bi

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Thanks @Pragati11 but unfortunaly this didn't solve my issue.
I did something similar in my measure. I have two slicers that are used as "parameters" to calculate the measure "dynamically".
One "parameter" if for ActualCosts/BudgetCosts, the other is used to displays values in Euros or Euros/Hectares or Euros/Acres.
To do that I use Two SWITCH functions one inside the other, and it works well.
I only need to re-format the first values, the ones in Euros, and leave the others "as is", because they are already displayed correctly with a comma for decimals...

plv.jpg

Hi @autodafe ,

 

I am not clear here. does the measure which you wrote above solve your issue?

If not then - Is it possible to share some sample data and the calculations you are using so that I can try it on my side? If sharing pbix is possible that would be great by removing any sensitive information.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

The measure works OK as far as calculations are concernded, but the FORMAT function doesn't work correctly.
The other calculations without the FORMAT function are showing fine, with commas for decimals and dot for thousands.

Unfortunalty I cannot provide the .pbix file but I can try to create something simpler as an example

 

mmmm....
i created a new file with sample data and created a similar measure...and it works correctly.
This made me think at how the model was created. I am afraid I created the model using English language and then I switched to Italian afterwards (this is what happens when you have to teach people how to use PowerBI In different languages) and AFAIK once the model has been created with a language/regional setting...it cannot be changed...
Here's a .pbix file with sample data, and it works correctly

autodafe.net/tmp/PBI_example.pbix 

Hi @autodafe ,

 

I just checked the pbix file that you shared and I see the measure is showing the right value in right format:

format.gif

 

Is this working in the same way in your original pbix file?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

autodafe
Resolver I
Resolver I

thanks @amitchandak , but as I already said regional settings are already set to Italian

 
 

regional.jpg

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.

Top Solution Authors