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.

[Barny]

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

[Barny]

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]

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.

[blindeye]

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?

[KGrice]

Both will work, but I like @Anonymous'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:

[KGrice]

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: