Forum Discussion
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.
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.
10 Replies
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)And them add a table to get the repeated name
DuplicateName = (SUMMARIZE(FILTER(JobRunDocket,JobRunDocket[IFDuplicate]>0),JobRunDocket[DOC_RUN_ID]))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.
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.
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.
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.
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.
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.
In old days it was done by EARLIER function but now you can use varibales to do it.
You can watch this video too.
https://www.youtube.com/watch?v=JI-QUASKuUw
