DAX Format EU numbers

[PVO3]

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.

[Anonymous]

Hi @PVO3 ,

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

Best Regards,

Jay

[PVO3]

@Anonymous 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.

[Anonymous]

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

[PVO3]

No solution yet.

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

[amitchandak]

@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]

@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]

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.