Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Counting Unique Duplicate Email Addresses

Is there a way to count the number of unique email addresses in a column that are duplicated?

 

I want to get the same number as Excel when you remove duplicates; e.g. 114 duplicates were removed. 14393 records remain. 

 

I think the latter number I can get just by a DISTINCTCOUNT, but the number of duplicates being removed is tricky as there are 2 of some, and more of others so can't do a simple divide. 

 

Help!

Need Help
Tips and Tricks
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    If you need distinct count of duplicated rows, try a measure

    Measure = COUNTROWS(
FILTER(
SUMMARIZE('Table', 'Table'[Column1], "Count", COUNT('Table'[Column1])),
[Count] > 1
)
)

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    Anonymous 

    you can use countrows() - distintcount(). Then the result is the duplicated values will be removed.

     

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    If you need distinct count of duplicated rows, try a measure

    Measure = COUNTROWS(
FILTER(
SUMMARIZE('Table', 'Table'[Column1], "Count", COUNT('Table'[Column1])),
[Count] > 1
)
)
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    answers above seems helpful.

    If your problem is sloved, please feel free to accept any of them as a solution so to close the case.

    If not, please tell me other details to explore this problem.

     

    Best Regards

    Maggie