space as separator 1000 / thousands

[lucyf]

I have a column "id" where there are no unique values, there is a number 11 characters long as text because I need to use it as count(distinct) = count ids. The result is a number that has no space as a separator 1000 (### ###), is it possible to solve this? I am begginer.

[lucyf]

Hi Rongtie,

It is not my issue. My english is not very strong, but I will try to explain it better.

I have columned named „id“, which represents the accident number.  However there can be two or three persons in one accident, so „id“ is not unique. „id“ contains 11-digital number with a text data type.

I´d like to create a visualization where I count the number of accidents without duplicates. I input the „id“ into the visualization, change the field to count (distinct) and this number doesn´t have space between 1000. And my question is: It is possible change this number with space? I don´t think so.

But now I have an idea, to create new column, where there is a „1“ for the first accident number, and „0“ for other same accident numbers, than the result will be number and I can format it as number with space, it is not problem.

But I will need to come up with the DAX formula for this, I hope it  is possible 😊

PS: Is it clear or shall I prepare pbix file? Lucy

[v-rongtiep-msft]

Hi @lucyf ,

Thank you for reaching out for assistance with Power BI. If I understand correctly, you have a column with ID values that are 11 characters long, and you would like to format these numbers to include a space as a thousands separator. Here's how you can achieve this in Power BI:

Formatted ID = FORMAT(VALUE([ID]), "## ### ### ###")

Replace [ID] with the actual name of your column. This formula converts the text to a number using VALUE and then formats it with spaces as thousands separators using FORMAT.

Press Enter to create the column.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lucyf · Accepted solution]

Once more Hi Rongtie,

probably I have easier solution and new measure 

CountUnique = COUNTAX(VALUES(Sheet1[id]), 1)

It counts each first number and it is number, so I can format it 🙂 

I am begginer and I don´t know very much DAX 🙂

Thank you for you help 🙂