Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Niels_T
Post Patron
Post Patron

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

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.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

PhilipTreacy
Super User
Super User

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])

 

uniq-ord.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Ashish_Mathur
Super User
Super User

Hi,

This measure should get you your desired result

=distinctcount(data[order_id])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.