cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbiuser2 Member
Member

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@pbiuser2 

 

Also you can use this MEASURE

 

Measure =
COUNTROWS ( EXCEPT ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) ) )
3 REPLIES 3
Highlighted
DoubleJ Member
Member

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

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

Super User
Super User

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

@pbiuser2 

 

Also you can use this MEASURE

 

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

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

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