Find matching value in non-related table

[TSI]

Hi Community,

I'm new to DAX and struggling to do a 'vlookup' to find matching values in tables that are not related.

I'm working with 2 databases:

• Master database - contains records of all employees in the company (i.e. Unique Employee ID)
• Successor database - keeps track of employees (current position holder) and their successor.

Unfortunately the Successor database is quite messy, with records where Employee ID (Holder) = Employee ID (Successor), which is redundant data. I have added  a column "Is Real?" with Yes/No to differentiate the real successor  records.

Master databaseSuccessor database

Required solution:

To find out if an employee is also a successor, I want to add a "Is Successor" column in the Master database.

It needs to match the value of Employee ID + Month in the Master Database, to Employee ID (Successor) + Month (only looking at records where Is Real Successor = Yes) in Successor database.

Where it finds a match, to return "Yes", else "No":

Is Successor column (solution)
   
 
   
     
   
   
     

I've tried using Lookupvalue, but it didn't work because the relationship is between Master Database [Employee ID] and Successor Database [Employee ID (Holder)]. 

Using Query/Merge would be a more elegant solution, unfortunately it slows down the PBI desktop file considerably due to the huge Master database size.

Attached is the PBI file for reference.           

Many thanks in advance for your insights and guidance!

Best regards,

Kim

[Stachu · Accepted solution]

look at this article, it has examples for a measure that would use TREATAS/INTERSECT

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
EDIT actually added the link to the article

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

[TSI]

@Stachu 

Thanks! Would you have the link to the article please?