Forum Discussion
count common values for a columns from two tables
Hi,
I have two tables which has EmailID field, I need to get the count of common EmailIDs from both the tables:
Table1
| Users |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
Table2
| ActiveUsers |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
I want to compare Users from Table1 to ActiveUsers from Table2 and get the count of only those Users from Table1 which are present in Table2. So, I can get the count of those Users only from Table1 which are active as per Table2.
Thanks
Anonymous Perhaps:
Measure = COUNTROWS(INTERSECT(SELECTCOLUMNS('Table1',"Users",[Users]),SELECTCOLUMNS('Table2',"Users",[ActiveUsers])))Hi Anonymous ,
You can create a measure like below:-
Measure = CALCULATE ( DISTINCTCOUNT ( _users[Users] ), FILTER ( _users, _users[Users] IN VALUES ( _ActiveUsers[ActiveUsers] ) ) )Thanks,
Samarth
10 Replies
- Greg_Deckler
Community Champion
Anonymous Perhaps:
Measure = COUNTROWS(INTERSECT(SELECTCOLUMNS('Table1',"Users",[Users]),SELECTCOLUMNS('Table2',"Users",[ActiveUsers]))) - amitchandak
Super User
Anonymous , Create a common user table
Users = distinct(union(distinct(Table1[Users]),distinct(Table2[ActiveUsers])))
the have two measure
T1= count(Table1[Users])
T2 = Count(Table2[Users])
Both in A and B
both = counts(filter(values(users[User]), not(isblank(T1)) && not(isblank(T2)) ))
Samarth_18Hi Anonymous ,
You can create a measure like below:-
Measure = CALCULATE ( DISTINCTCOUNT ( _users[Users] ), FILTER ( _users, _users[Users] IN VALUES ( _ActiveUsers[ActiveUsers] ) ) )Thanks,
Samarth
Hi Samarth_18 ,
I am getting the result like this:
[email protected],[email protected],[email protected],[email protected]
but i need them in seperate row when placing this meausre into a table Visual:
Measure
[email protected] [email protected] [email protected] [email protected] [email protected] [email protected] Thanks
- Samarth_18
Anonymous , I dont think its possible in this way but you can create a seperate table with below code:-
intersect(SELECTCOLUMNS('_users',"Users",[Users]),SELECTCOLUMNS('_ActiveUsers',"Users",[ActiveUsers]))
Greg_Deckler Thank you for the solution, it works fine.
As we are getting count here, Can you please help me how to get the list of these common emailids also?
Thanks
- Greg_Deckler
Anonymous Replace COUNTROWS with CONCATENATEX(...,[Users],",")
It's giving error when replaced COUNTROWS with CONCATENATEX:
"Too few arguments were passed to the CONCATENATEX function. The minimum argument count for the function is 2."
Measure = CONCATENATEX(INTERSECT(SELECTCOLUMNS('Table1',"Users",[Users]),SELECTCOLUMNS('Table2',"Users",[ActiveUsers])))thanks
