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
PVO3
Impactful Individual
Impactful Individual

DAX Format EU numbers

Hello,

 

I'm looking for a solution to format my numbers to EU standards in DAX. This is mainly concerning thousand seperators.

Read a lot of articles about converting my numbers to text, then replacing comma with period. But this causes many issues like slowing down my reports.

 

So to be clear an example. How to convert 100000.00 in €100.000 by using DAX? This without converting the datatype to text.

 

This should be easy but FORMAT( , "Currency") or FORMAT ( , "€ #,##0") give inccorrect results. FORMAT even converts to text, which is not allowed.

 

My system is using the desired number format, but DAX just won't let me.

 

 

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

Hi @PVO3 ,

 

You could simply change the format of the column to Currency and choose a currency type.

9.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
PVO3
Impactful Individual
Impactful Individual

@v-jayw-msft thanks for your reply. Like I mentioned I need a DAX solution. Your suggestion is static. What if I would like to use the measure for different scenario's? By using FORMAT i can do some adjustments but it has 2 downsides. I can't change the thousand seperator and it converts to string.

Hi @PVO3 ,

 

I don't quite understand the scenario. You could always use the data in measures or calculated columns and all you need is to change its display format.

And as far as I know, there isn't a DAX function could get a numeric value with thousand seperator. 

Hope others have a better solution.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
PVO3
Impactful Individual
Impactful Individual

No solution yet.

It's hard for me to understand why such a basic functionality seems to not be available. 

amitchandak
Super User
Super User

@PVO3 , ideally speaking regional format should change it.

 

Try giving format under properties in data model view and try

for steps refer

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

PVO3
Impactful Individual
Impactful Individual

@amitchandak thanks for your reply!

Unfortunatly I'm looking for a DAX solution. For instance to create a measure that displays both displays currency and whole numbers. 

Jorenvdl
Frequent Visitor

As far as I know this is not possible. We have created a work around for the currency by creating a column that contains the currency icon. Using a filter, dimension table and a measure with SELECTEDVALUE you can give the user the possibility to choose the currency of his preference.

I am not sure how to achieve this with the decimal character.

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.