cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daveed1973 Regular Visitor
Regular Visitor

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

Accepted Solutions
scottsen Senior Member
Senior Member

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

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

17 REPLIES 17
KGrice Established Member
Established Member

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

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

scottsen Senior Member
Senior Member

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

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

KGrice Established Member
Established Member

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

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

Daveed1973 Regular Visitor
Regular Visitor

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

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

KGrice Established Member
Established Member

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

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

blindeye Regular Visitor
Regular Visitor

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

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?

andrewt Regular Visitor
Regular Visitor

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

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

andrewt Regular Visitor
Regular Visitor

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

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

MB2 Frequent Visitor
Frequent Visitor

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

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)