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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TannerBuck7
Microsoft Employee
Microsoft Employee

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 Employee
Microsoft Employee

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

9 REPLIES 9
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 Employee
Microsoft Employee

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 Employee
Microsoft Employee

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,

 

 

Hello! 

 

I want to calculate distinct values of column A. Which i did by using the distinct function in a measure I created. 

 

Now I want to calculate the total number of "No" for each unique value only. Can anyone please help? @ImkeF  @v-caliao-msft 

 

For example the total should be 3 for "No" as I want to count for Saturday as only 1 not 2 times. 

Capture111.PNG

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors