Forum Discussion

PVO3's avatar
PVO3
Impactful Individual
5 years ago

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.

 

 

Need Help

7 Replies

    • PVO3's avatar
      PVO3
      Impactful Individual
      5 years ago

      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's avatar
        Jorenvdl
        Frequent Visitor
        5 years ago

        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.

  • PVO3's avatar
    PVO3
    Impactful Individual
    5 years ago

    No solution yet.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi PVO3 ,

     

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

     

    Best Regards,

    Jay

    • PVO3's avatar
      PVO3
      Impactful Individual
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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