cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
autodafe
Helper II
Helper II

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!

13 REPLIES 13
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 III
Super User III

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





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


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





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


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





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




autodafe
Helper II
Helper II

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

 
 

regional.jpg

amitchandak
Super User IV
Super User IV

@autodafe , I think . vs , comma as thousand seprator is regional setting.

 

refer: https://community.powerbi.com/t5/Desktop/Thousand-Separator-and-Decimal-Separator/td-p/194928

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=8f5f4d25-...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors