Lookup table with multiple criteria

Hi TSI ,

You can create column Master & Month in table Master Database, create column Successor & Month in table Successor Database.

Master & Month = CONCATENATE('Master Database'[Employee ID],CONCATENATE(" ",'Master Database'[Month]))

Successor & Month = CONCATENATE('Successor Database'[Employee ID (Successor)],CONCATENATE(" ",'Successor Database'[Month]))

Then create measure Is Successor? in table Master Database.

Is Successor? = IF(MAX('Master Database'[Master & Month])IN VALUES('Successor Database'[Successor & Month]),"Y","N")

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi v-xicai ,

Thank you for looking into my question, I'm grateful for the help!

I added the column Is Successor in the Master Database, the results show "Yes" for all employees:

For January, Employee IDs 1010 and 1020 should be "No"?

Any idea why this happened?

Here's the updated PBI file for reference.

Thanks again Amy.

Best regards,

Kim

TSI 

Hi,

I have a different approach on this and have used Power Query instead.

Please check the file here

Thanks,

Vivek

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi TSI ,

>>I'm trying to add a measure to the Master Database: Is Successor?  

As you said, would you like to create a measure instead of a calculated column? while it is a calculated column seen from the picture you showed above.

So try to create a measure Is Successor? in table Master Database like DAX below.

Is Successor? = IF(MAX('Master Database'[Master & Month])IN VALUES('Successor Database'[Successor & Month]),"Y","N")

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.