Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Also you can use this MEASURE

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Also you can use this MEASURE

 

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

Regards
Zubair

Please try my custom visuals
DoubleJ
Solution Supplier
Solution Supplier

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.