Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

calculation help

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

UserEmail
john[email protected]
johna,[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_IDSenderEmailReceiverEmailReceivedDate
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
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    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.

1 Reply

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    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.