Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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...
@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
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
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.
Thanks,
Pragati
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:
Is this working in the same way in your original pbix file?
Thanks,
Pragati
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |