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!
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)
Hi @autodafe ,
There is a blog on how you can dynamically chnage the format of the data to be displayed. Check if this helps:
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...
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.
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
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
Hi @autodafe ,
I just checked the pbix file that you shared and I see the measure is showing the right value in right format:
Is this working in the same way in your original pbix file?
@autodafe , I think . vs , comma as thousand seprator is regional setting.
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.
See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.