Count values not in another table using Powerpivot

Question

I have two tables in Powerpivot, Table A & Table B. They both contain keys, yet no Primary Key. What I am trying to do is work out how many of the keys in Table B, are not in Table A.

My thoughts so far have been to use DAX (which I am very new to), or a SQL command like this SELECT A.key FROM A WHERE key NOT IN(SELECT key FROM B).

However, I cannot figure out the correct DAX formula, and despite the SQL query logic being correct, I cannot get this to work in Powerpivot.

I would also be open to a 'countif in Table B column'...

Any help would be great.

Thanks - Chris

Answer 1

Solved it with a bit of help from this page. If there is a match, the value is 1, if not, then 0.

=if(LEN(CALCULATE(values(TableA[Key]),filter('TableA',TableA[Key]='TableB'[Key])))>1,1,0)