Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to 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.
@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.
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
Proud to be a Super User!
Hi,
This measure should get you your desired result
=distinctcount(data[order_id])
Hope this helps.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
79 | |
67 | |
60 | |
45 | |
45 |