NOT IN measure for direct query

[admin_xlsior]

Hello guys,

What would be the DAX measure in Direct Query looks like if I have two tables which I want to count records with some value not in other table.

So I have something like this :

I want to count number of Sales in TableA, but if the SalesID is not in TableB [RecallSalesId]

And this tables is not relate each other in matter of Relationship diagram, since it is 2 fact tables.

Thanks

[amitchandak · Accepted solution]

@admin_xlsior ,

calculate(countrows(Table1), filter(Table1, not(Table1, Table1[SalesID] in values(Table2[SalesID]))))

Try like, but this can really costly in the direct query or might not work

[admin_xlsior]

Hmm.. yah.. I can see it's costly since it is distinct all TableB with VALUES. 

But I think it is no choice. 

It works.

Thanks,