Forum Discussion
Counting Union/Duplicates in a table or multiple tables
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?
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.
3 Replies
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.
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!!
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.
