How do I count the number of rows containing specific values in a different table?

[Anonymous]

I need to write a measure that gives me the distinct count of items in one table that do not appear in another table.

Table1 appears as follows:

ID 

1001

1002

1003

1004

Table2 appears as follows:

ID        

1001

1002

1004

1001

1002

I want to write a measure that returns a result of 1 because ID 1003 in Table1 does not appear in Table2.

Any and all help would be greatly appreciated.

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Also you can use this MEASURE

Measure =
COUNTROWS ( EXCEPT ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) ) )

[DoubleJ]

You could do this in 2 steps:

1.  Create a calculated column in Table1 that looks up if the ID exists in Table2:
   

   IsInTable2 = LOOKUPVALUE(Table2[ID],Table1[ID],Table1[ID])

   You can hide this column as it only stores an intermediate result.
   
   
2. Create a measure that counts the Blanks in the created colum
   

   NotInTable2 = COUNTBLANK(Table1[IsInTable2])

Hope this helps

JJ

[Anonymous]

Thanks for the suggestion. I appreciate you taking the time to submit it.