Forum Discussion

TomLU123's avatar
TomLU123
Helper III
8 years ago
Solved

Visualize the duplicate records in Data Set

Dear  Experts, 

 

I have a data set like below to track the Event Participant Name and their Participant ID. 

I wish to identify those duplicate ID and respective Participants so that we can clean the data. 

  1. Is there any way that can show the impacted people who are assigned a duplicated ID? In this example, following people should display. 
  2. Is there any way to count the number of duplicate ID? For example, in this example, there are two ID duplicated (A1111, B22121)

 

Many thanks!

 

Best regards,

Tom

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi TomLU123

    create measures as below

    count = CALCULATE(COUNT(Sheet4[id]),FILTER(ALL(Sheet4),[id]=SELECTEDVALUE(Sheet4[id]) ))

Measure = CALCULATE(DISTINCTCOUNT(Sheet4[id]),FILTER(ALL(Sheet4),[count]>1))

    "count" - the numbers of each ID

    "Measure" - count the number of duplicate ID

     

    Then add "count" to the Visual Level Filter as the picture shown

     

     

     

    Best Regrads

    Maggie

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi TomLU123

    create measures as below

    count = CALCULATE(COUNT(Sheet4[id]),FILTER(ALL(Sheet4),[id]=SELECTEDVALUE(Sheet4[id]) ))

Measure = CALCULATE(DISTINCTCOUNT(Sheet4[id]),FILTER(ALL(Sheet4),[count]>1))

    "count" - the numbers of each ID

    "Measure" - count the number of duplicate ID

     

    Then add "count" to the Visual Level Filter as the picture shown

     

     

     

    Best Regrads

    Maggie