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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

26 REPLIES 26
Sarah15
New Member

Worked even after many years. Only things which i changed was removing >0 condition. 

 

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

 

Hi 

Is there a DAX to find out if the values in column A of Table 1 are blank, then to give the values from the column B from Table 2 otherwise give the values from Column A from Table 1?
Thanks

Is there a way to then delete the rows where this column == True? I want to delete the duplicates from my power query data, but the column using this function doesn't appear in the power query editor

Hi,

Share some data to work with and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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