How to count unique value and add 1 in new column if it is distinct value else 0

[Niels_T]

Hello,

I have a table with an order_id column. This is a table full of order row lines so there are many order_id's that contain the same value.

So my question is, how do I make an extra column where all unique values in "order_id" get 1 and the duplicate a 0?  

I want to achieve this result so that I can count all the 1's and then get an accurate number of how many orders we have.

[parry2k]

@Niels_T in my opinion, @lbendlin already answered the question, not sure how other answers are different. 

[PhilipTreacy]

Hi @Niels_T 

Download sample PBIX file with data and measure

This measure will give you the result you want. No need to create extra columns and make your dataset larger.

Unique Orders = DISTINCTCOUNT('Table'[order_id])

Regards

Phil

[Ashish_Mathur]

Hi,

This measure should get you your desired result

=distinctcount(data[order_id])

Hope this helps.

[lbendlin · Accepted solution]

You may be overcomplicating this.  Power BI already has a Count (Distinct) option. But if you really want that extra column then you need to have some sort of index column so you can properly tag the "first"  and "not first" rows for each order.

by the way, "all unique values in "order_id" get 1 and the duplicate a 0"  is ambiguous. I think you meant to say something different.