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	[email protected]
john	a,[email protected]
john	[email protected]
Mark	[email protected]
Mark	[email protected]
Dave	[email protected]
Dave	[email protected]
Dave	[email protected]
Craig	[email protected]
Tony	[email protected]
Pete	[email protected]

Table2

Msg_ID	SenderEmail	ReceiverEmail	ReceivedDate
1	[email protected]	[email protected]	6/10/2019
2	[email protected]	[email protected]	6/12/2019
3	[email protected]	[email protected]	6/20/2019
4	[email protected]	[email protected]	6/23/2019
5	[email protected]	[email protected]	6/25/2019
6	[email protected]	[email protected]	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.