Specifying thousand separator dax function

[Anonymous]

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

[Anonymous]

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

[v-gizhi-msft · Accepted solution]

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]

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

[Anonymous]

Measure 1 = FORMAT(COUNT(YourTable[Your_Column]), "##,###")

Measure 2 = SUBSTITUTE([Measure 1], ",", ".")

Now you can use Measure 2 in your graphs

[Anonymous]

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]

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]

Hi @Anonymous 

change windows regional setting

https://support.office.com/en-us/article/change-the-windows-regional-settings-to-modify-the-appearan...

And set seperator as dot.

Apply chnages and open pbix file again.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Hi @Anonymous 

I think you want decimal as comma seperaor?

Check this,

https://community.powerbi.com/t5/Desktop/Thousand-Separator-and-Decimal-Separator/td-p/194928

one more way is

measure=format([measure1],"##.###")

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

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]

Hi @Anonymous 

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

You need to change region setting.

Thanks,

Pravin

[Anonymous]

https://community.powerbi.com/t5/Desktop/change-format-by-using-dot-instead-of-comma/m-p/372752/high...

[Anonymous]

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 ?