cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daveed1973
Advocate II
Advocate II

DAX query to compare a value in one table to see if it exists in another table

In Power BI desktop I have two sets of data, both of which have a field called "Job Number". I would like to be able to create an extra column in one of the tables which has a simple "True" or "False" value depending on whether a job number value in the first table exists in the second table.

 

So for example, if in Table1 there is a Job Number value of 123456 and that value also exists in the Job Number field in Table2 then a new column called Matched should have the value of "True". However, there may be multiple instances of that Job Number value in each of the tables.

 

Hope that makes sense. Any help will be appreciated.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I think a calc column on table 1 that looks similiar to this should work:

= CALCULATE( COUNTROWS(Table2), FILTER( Table2, Table2[JobNum] = EARLIER(Table1[JobNum) ) ) > 0

 

View solution in original post

22 REPLIES 22
costosmaya
New Member

Awesome after all this time @Anonymous's solution still works, thanks a lot!!

Anonymous
Not applicable

I think a calc column on table 1 that looks similiar to this should work:

= CALCULATE( COUNTROWS(Table2), FILTER( Table2, Table2[JobNum] = EARLIER(Table1[JobNum) ) ) > 0

 

Five and a half years later and this solution still does it! Thanks for posting this.

Worked great - thank you for the help!

i just tried this for a similar use case,


@Anonymous wrote:

I think a calc column on table 1 that looks similiar to this should work:

= CALCULATE( COUNTROWS(Table2), FILTER( Table2, Table2[JobNum] = EARLIER(Table1[JobNum) ) ) > 0

 


But i recieve this error:

 

A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Anonymous
Not applicable

This is giving me error. " A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Anonymous
Not applicable

Please delete >0 it is not required 


@Anonymous wrote:

I think a calc column on table 1 that looks similiar to this should work:

= CALCULATE( COUNTROWS(Table2), FILTER( Table2, Table2[JobNum] = EARLIER(Table1[JobNum) ) ) > 0

 


 

@Anonymous  This solution worked as wonder. Thanks for sharing it.

ilan
Frequent Visitor

Hi Scott,

I am trying to implement your solution but I am getting 'A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'

I am using a calculated column per your instructions. thank you!

Anonymous
Not applicable

Have you had any luck finding the cause of this error, or a solution?

Hi There -- this returns "true" and "false", but I cannot conditionally format on text at the moment. Is there a way to return "0" or "1" instead? 

Anonymous
Not applicable

I get an error: EARLIER/EARLIEST refers to an earlier row context which doesn't exist. 

I got this error when trying to create a measure, rather than a calculated column. If you're still working on this five years later (lol), give it a shot.

Thnaks guys for the help, Scottsen's solution has worked a treat even though I still don't quite understand what the query is doing lol

@Daveed1973

The CALCULATE function in this case is like a wrapper that allows you to modify how another function is calculated. For this formula, you're calculating the rows in Table2, but you need to modify how that calculation is done. If you just used COUNTROWS(Table2) by itself, you'd get the total number of rows for that table showing up for every row in Table1, i.e., if there were 10 rows in Table2, you'd have a column that showed 10 in every row of Table1.

 

That's where FILTER comes in. FILTER allows you to filter a table by a set of criteria. In this case, you're filtering Table2, to only count the rows in Table2 where the Job Number in Table2 is the same as the Job Number of the current row in Table1. The EARLIER function is typically used to go back to an earlier context, kind of like an escape from your FILTER function. It's actually not necessary in @Anonymous's answer this time, since the FILTER function isn't affecting Table1. You could therefore simplify the formula a little and end up with this:

 

= CALCULATE(COUNTROWS(Table2), FILTER(Table2, Table2[JobNumber]=Table1[JobNumber])) > 0

 

 

Hopefully that helps for next time!

Anonymous
Not applicable

I get an error for this calculation as well: "A single value for column X in table 'Table1' cannot be determined. 

It can work as a measure using the following syntax to get a distinct list of id's to join on:

 

Application Count = CALCULATE(DISTINCTCOUNT('Application'[ApplicationID]),FILTER('Application','Application'[ApplicationID] IN DISTINCT('Measures'[ApplicationID])))

It can work a a Measure using the following Syntax to get distinct values for the comparison operator:

 

Application Count = CALCULATE(DISTINCTCOUNT('Application'[ApplicationID]),FILTER('Application','Application'[ApplicationID] IN DISTINCT('Measures'[ApplicationID])))

MB2
Frequent Visitor

I know this discussion is old but just to close the discussion for some people as I too kept running into this same issue. I kept running into the same issue until I figured out that the solution makes reference to a Calculated Column (using DAX) rather than a Measure (using Dax). It doesn't work as a Measure.

KGrice's code above has worked beautifully for me, but I'm wondering how I can add an additional filter to this calculation.

 

= CALCULATE(COUNTROWS(Table2), FILTER(Table2, Table2[JobNumber]=Table1[JobNumber])) > 0

 

So my calculation has found the items in table2 that have a match in table1. What I would like to do is only find those table2 results where another field in table2 equals a specific value.

 

In my scenario, Table1 is a list of jobs. Table2 is a list of complaints where one column designates whether the complaint was considered Justified after investigation with a Yes/No answer, so my aim is to show on a chart the percentage of jobs on which there was a justified complaint, rather than just an entry in the complaints table.

 

Can I add a clause to this code, or do I need to use a different DAX function?

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors