Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Specifying thousand separator dax function

Hello, 

 

I currently have a measure that makes a count. and what I wanted was for the thousands to be separated. but the only option that appears to me is to put the comma.

 

 
 

 

 

 

does anyone have any idea how to put a dot instead of the comma?

 

Best Regards,

JO

Need Help
Show and Tell
Tips and Tricks
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    I create a sample as below:

    Then i create a measure to test:

    Measure 1 = SUM('Table'[Value])

    Then try this measure:

    Measure 2 = 
VAR t =
    SUM ( 'Table'[Value] ) & ""
VAR t1 =
    GENERATESERIES ( 1, LEN ( t ) + ROUNDDOWN ( LEN ( t ) / 3, 0 ), 1 )
VAR t2 =
    ADDCOLUMNS (
        t1,
        "Char", IF (
            [Value] / 4
                = ROUND ( [Value] / 4, 0 ),
            ".",
            MID ( t, LEN ( t ) - [Value] + 1 + ROUNDDOWN ( [Value] / 4, 0 ), 1 )
        )
    )
VAR result =
    CONCATENATEX ( t2, [Char], "", [Value], DESC )
RETURN
    result

    The result shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

     

    I have found a short version for the measures that works good for me. I prefer using recommended DAX separators but it has its disadvantages such as a comma as a thousand separator. See measure below to solve this issue.

     

    Measure 1 =
    var _maxScore = MAX(Score[Score])
    var _formatScore = FORMAT( _maxScore, "#,###" )
    var result = SUBSTITUTE( _formatScore, ",", "." )
    return
    result
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Measure 1 = FORMAT(COUNT(YourTable[Your_Column]), "##,###")
     
    Measure 2 = SUBSTITUTE([Measure 1], ",", ".")
     
    Now you can use Measure 2 in your graphs
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous , 

       

      I can't use this measure because I'm counting the whole numbers, I don't have decimal places ... it's not a decimal number...

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous , 

         

        yes i try all the reginal settings, but not work on my side .... that is why i'm look to find a way to this in DAX

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous , i'm sorry, but no. that is not what i'm looking for....

       

       

      what I want, is that instead of being separated by a comma(,) it's separated by a dot(.) (it's an integer without decimal places)

       

       

       

      Best Regards, 

      JO

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous 

         

        I have shared one link in last reply have you tried it?

         

        You need to change region setting.

         

        Thanks,

        Pravin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous , i try a lot of times the reginal settings, but is a dead end.... do you know how can i made this happen in dax ?

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    I create a sample as below:

    Then i create a measure to test:

    Measure 1 = SUM('Table'[Value])

    Then try this measure:

    Measure 2 = 
VAR t =
    SUM ( 'Table'[Value] ) & ""
VAR t1 =
    GENERATESERIES ( 1, LEN ( t ) + ROUNDDOWN ( LEN ( t ) / 3, 0 ), 1 )
VAR t2 =
    ADDCOLUMNS (
        t1,
        "Char", IF (
            [Value] / 4
                = ROUND ( [Value] / 4, 0 ),
            ".",
            MID ( t, LEN ( t ) - [Value] + 1 + ROUNDDOWN ( [Value] / 4, 0 ), 1 )
        )
    )
VAR result =
    CONCATENATEX ( t2, [Char], "", [Value], DESC )
RETURN
    result

    The result shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

    • noob_dax's avatar
      noob_dax
      New Member
      3 years ago

      Add 

      IF(MOD(LEN(t),3)=0, MID(result,2,len(result)), result)
      Or else for 3 paired digits like 424 you will get ,424
      Similary 646343 is shown as ,646,343 but should be 646,343