cancel
Showing results for
Did you mean:
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

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

Also you can use this MEASURE

```Measure =
COUNTROWS ( EXCEPT ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) ) )
```
3 REPLIES 3
Highlighted
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

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

Also you can use this MEASURE

```Measure =
COUNTROWS ( EXCEPT ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) ) )
```
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.