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

[Daveed1973]

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.

[Sarah15]

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

[costosmaya]

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

[Anonymous · Accepted solution]

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

[Benedictmooray]

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

[aislinn]

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

[Ashish_Mathur]

Hi,

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

[kennedy311]

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

[apsaunders5]

Worked great - thank you for the help!

[abehrmann]

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]

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]

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

---

[Sara15]

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

[ilan]

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]

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

[andrewcaruso]

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]

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

[kennedy311]

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.

[Daveed1973]

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]

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

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