Forum Discussion

TannerBuck7's avatar
TannerBuck7
Microsoft Employee
10 years ago
Solved

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...
Need Help
  • TannerBuck7's avatar
    TannerBuck7
    10 years ago

    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.

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
10 years ago

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,

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    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.

  • newpowerbiuser8's avatar
    newpowerbiuser8
    Helper I
    3 years ago

     

     

    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.