Counting Union/Duplicates in a table or multiple tables

[Stavros2024]

Hi everyone,

I have one large table (30K plus rows) with 3 headings - Full Name, State and Cell Phone.  What I am trying to do is to count the number of Contacts based on the Cell Phone that are duplicated in more than one state

This works on 2 tables - I also have the large table broken up into individual states.  I then use the intersect function which works on 2 tables, but not across multiple tables

NY & CA All Duplicates by Cell =

VAR NYCell = VALUES('NYTable'[CellPhone])

VAR CACell = VALUES('CATable'[CellPhone])

RETURN

COUNTROWS(INTERSECT(NYCell,CACell))

The visual ends up like this. 

So in this case, there are 309 contacts that have the same cell across NY and CA across 15K+ contacts

What I want to be able to do is eventually have a slicer which can show how many contacts depending on which states are chosen

Example

NY/CA - 3  Between NY and CA, there are 3 contacts with the same cell

NY/CA/Tx - 1  Between NY/CA/Tx there is 1 contact with the same cell

CA/Tx - 2   Between CA/Tx  there are 2 contacts with the same cell

Any ideas on how to tackle this, either via DAX or adding a column?

[Selva-Salimi]

Hi @Stavros2024 

you can write a measure as follows:

measure duplicated := var tbl = summarize (your_table , State,  "duplicated" , distinctcount(Cell Phone))

var tbl2 = summarize ( filter(tbl , duplicated >=2) , state)

return

countrows (tbl2)

this will be return count of duplicated cells in selected states in slicer.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

[Stavros2024]

Hi @Selva-Salimi it is so close to what I need, ATM it's giving me a total (I think of the type of Contacts) by those who are in the number of states

Blanked out the names to for privacy

So the there are a bunch of names that are in 4 states that are duplicated, there are another bunch in 3 states

What's an easy way to get the total of them all?  That is how do I sum the "All Duplicated" Column?

Thanks so much!!

[Selva-Salimi · Accepted solution]

@Stavros2024 

you can write a measure as follows:

measure _dupicate_count := var tbl= summarize ( your_table , full_name ,"total" , [all duplicate])

return

sumx(tbl , total)

and this measure to a card. 

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.