cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper 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
Memorable Member
Memorable Member

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

18 REPLIES 18
Memorable Member
Memorable Member

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

i just tried this for a similar use case,


@scottsen 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 


@scottsen 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

 


 

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

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!

Regular Visitor

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? 

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

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 @scottsen'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!

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])))

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?

Both will work, but I like @scottsen's answer. Much more direct and probably less overhead, unless you end up needing the additional query for something else. I was trying to think through it from a relationship perspective, but you get the same result either way:

 

JobIDs2.PNG

Solution Sage
Solution Sage

One way to do this is when you have repeating values in each table is to create another query that just contains the unique IDs of the second table. If you have Table1 and Table2 now, you can duplicate Table2 in the query editor, reduce it down to just the Job Number column, and use the Remove Duplicates feature to leave you with a unique list of jobs from Table2. Call your new table something like Table2_UniqueJobNumbers. Since the source is the same, this list will stay current with everything in Table2.

 

From there, create a relationship from Table1 to Table2_UniqueJobNumbers, and create your Matched column:

 

Matched = NOT(ISBLANK(RELATED(Table2_UniqueJobIDs[JobIDsDEF])))

 

Results:

 

JobIDs.PNG

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors