Query with Not In (select * from table)

[Anonymous]

Hello, I need to create report using query with multiple not in statements.

select email
from table1 a
where a.email not in ( select email from table2 )
and a.email not in ( select email from table3 )

and a.email not in ( select email from table4 )

Where table1, table2,  table3 and table4 are not related

Im using power bi with Direct Query mode.

Any ideas/help is greatly appreciated!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on my testing, please try the following methods again:

1.Create the simple tables.

2.Create the new table to filter the email.

filtered_data = 
var tab2 = SELECTEDVALUE(Table2[Email])
var tab3 = SELECTEDVALUE(Table3[Email])
var tab4 = SELECTEDVALUE(Table4[Email])
RETURN
CALCULATETABLE('Table1', NOT('Table1'[Email] IN {tab2, tab3, tab4}))

3.Drag the new table into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[Anonymous]

Hello, sorry to take me so long to test your solution...

Unfortunately when i tried got this error - 

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Also, I can't open your pbix as we use older version...

Thank you any way, may be you know another way of doing that? Appreciate your help!!!