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
Anonymous
Not applicable

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.

 

image.png

 
 

 

 

 

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

 

Best Regards,

JO

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

I create a sample as below:

60.PNG

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:

61.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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
Community Support
Community Support

Hi,

 

I create a sample as below:

60.PNG

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:

61.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

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
Not applicable

Measure 1 = FORMAT(COUNT(YourTable[Your_Column]), "##,###")
 
Measure 2 = SUBSTITUTE([Measure 1], ",", ".")
 
Now you can use Measure 2 in your graphs
Anonymous
Not applicable

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
Not applicable

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
Not applicable

Hi @Anonymous 

 

change windows regional setting

 

https://support.office.com/en-us/article/change-the-windows-regional-settings-to-modify-the-appearance-of-some-data-types-edf41006-f6e2-4360-bc1b-30e9e8a54989

 

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
Not applicable

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
Not applicable

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

 

image.png

 

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
Not applicable

Hi @Anonymous 

 

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

 

You need to change region setting.

 

Thanks,

Pravin

Anonymous
Not applicable
Anonymous
Not applicable

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 ?

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.