cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TannerBuck7
Microsoft
Microsoft

Count duplicate values

Is there a way to count the number of rows in a column that have duplicate values? I have a column with a large list of names and I just want to count the number of names that are repeated and return that number on a card.

1 ACCEPTED SOLUTION
TannerBuck7
Microsoft
Microsoft

I figured out a solution that worked for my data.

 

I created a calculated measure that subtracted the DISTINCTCOUNT of the Names from the DISTINCTCOUNT of the unique identifier in the table.

 

The difference is the amount of names with multiple rows in the table.

View solution in original post

8 REPLIES 8
AJ007
Helper I
Helper I

I actually came across a brilliant and the most easy way to do this.

 

All you have to do is first select a column from which you want to find duplicate text. Then select any another associated column. This will give you a table like structure.

 

Now in the "Values" area, select that second column and go "Count".

 

You can now filter greater than or equal to 2.

 

You can then create another visual so it filters through as per your selection.

TannerBuck7
Microsoft
Microsoft

I figured out a solution that worked for my data.

 

I created a calculated measure that subtracted the DISTINCTCOUNT of the Names from the DISTINCTCOUNT of the unique identifier in the table.

 

The difference is the amount of names with multiple rows in the table.

Anonymous
Not applicable

You could also use COUNTROWS as it is probably quicker and you can use the measure as a check that your primary key contains unique values in case it comes from an unvalidated source.

Anonymous
Not applicable

could you please write down the formula?

v-caliao-msft
Microsoft
Microsoft

Hi Tanner,

 

According to your description, you need to count the number of names that are repeated, right?

 

I hae test it on my local environment, we can add a calculated column to check if the current name is repeated or not.
IFDuplicate = IF(CALCULATE(COUNT(JobRunDocket[DOC_RUN_ID]),FILTER(JobRunDocket,JobRunDocket[DOC_RUN_ID]=EARLIER(JobRunDocket[DOC_RUN_ID])))>1,1,0)
Capture.PNG

 

And them add a table to get the repeated name
DuplicateName = (SUMMARIZE(FILTER(JobRunDocket,JobRunDocket[IFDuplicate]>0),JobRunDocket[DOC_RUN_ID]))
Capture1.PNG

Then add a measure to count the row of this table.
CountDuplicateName = COUNT('DuplicateName'[DOC_RUN_ID])

 

Regards,

If it's just that one number you're after (which doesn't change with interactive selections on your report), I wouldn't add unnecessary data into my model. Just reference your table with a new query like this:

let
    Source = YourTable,
    #"Grouped Rows" = Table.Group(Source, {"Name", {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = List.Count(Table.SelectRows(#"Grouped Rows", each [Count] > 1)[Count])
in
    #"Filtered Rows"

 This will create just one number (number of names that contain duplicates) that you can put into your card.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@TannerBuck7 Just add a column "count" with the value of 1 for each name. Drop the name and the "count" column in a visual and you can choose to sum/count etc out of the box. It will automatically be aggregated to show you which names are duplicated.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thank you for your post.

This makes sense but is there a good way to display that number on a card? I was able to see the names that had more than 1 count value in a column chart but I want to create a field that I can attach to a card so that it will automatically update with the count of repeat names.

 

 

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors