calculation help

[Anonymous]

i have two tables,

Table1  and  Table2  as below.

Table1 have 'User' and 'Email' columns.  Each  User will have multiple 'Email' s associated with them.

Table2 have 'Msg_ID', 'SenderEmail', 'ReceiverEmail', 'ReceivedDate' columns. 

I want to find  all the 'Users' from  'Table1' who neither send nor received any emails.

So, from below table, we can see  'Mark'  and 'Pete'  are the only users who does not have record in Table2 either in SenderEmail or ReceivedEmail.

Table1

User	Email
john	john@abc.com
john	a,john@abc.com
john	john@xyz.com
Mark	mark@abc.com
Mark	mark@xyz.com
Dave	dave@abc.com
Dave	a.dave@abc.com
Dave	dave@xyz.com
Craig	craig@abc.com
Tony	tony@abc.com
Pete	pete@abc.com

Table2

Msg_ID	SenderEmail	ReceiverEmail	ReceivedDate
1	john@abc.com	mike@comp.com	6/10/2019
2	dave@abc.com	joe@gmail.com	6/12/2019
3	joe@gmail.com	dave@abc.com	6/20/2019
4	rob@gmail.com	john@abc.com	6/23/2019
5	jeff@gmail.com	tony@abc.com	6/25/2019
6	craig@abc.com	alex@gmail.com	6/24/2019

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I create a sample using measures you can reference.

Measure = var s = VALUES(Table2[SenderEmail])
var r = VALUES(Table2[ReceiverEmail])
return
IF(MAX(Table1[Email]) in s || MAX(Table1[Email]) in r, 0,1)

Measure 2 =
VAR countr =
CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[User] ) )
VAR ck =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( ALLEXCEPT ( Table1, Table1[User] ), [Measure] = 1 )
)
RETURN
IF ( countr = ck, VALUES(Table1[User]), 0 )

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.