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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TSI
Advocate I
Advocate I

Lookup table with multiple criteria

 

 

Hi Community,

 

I have 2 tables - Master Database which contains employee details, and another Successor Database which shows if the employee has a successor. Both have recurring records each month.

Successor Table (sample).jpg

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

If Employee ID matches the Successor Database [Employee ID (Successor] in the specific month, return "Yes", otherwise "No".  Tables have many-to-many relationship. Unfortunately Lookupvalue didn't work for me.  

 

Please find the link to PBI file here.

 

Any guidance would be gratefully received. Thanks!

 

4 ACCEPTED SOLUTIONS
v-xicai
Community Support
Community Support

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.

View solution in original post

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:

Is Successor results.jpg

 

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

View solution in original post

vivran22
Community Champion
Community Champion

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

View solution in original post

v-xicai
Community Support
Community Support

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.

View solution in original post

7 REPLIES 7
v-xicai
Community Support
Community Support

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.

@v-xicai 

 

Hi Amy,

 

Just a quick question, if I wanted to create a column instead of a measure, how would the solution change?

 

Thank you for your time and effort and enlighten me!

 

Best regards,

Kim

@v-xicai 

 

Hi Amy,

Thanks for pointing out my error, I had not realised that it only worked as a measure, my apologies.

I tried it again as a measure and it worked beautifully. Thank you!

You've taught me how to use IN VALUE, which is totally new to me. Appreciate your help 🙂

 

Best regards,

Kim

vivran22
Community Champion
Community Champion

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

@vivran22 

 

Hi Vivek,

This is a creative approach, and it worked perfectly! Thank you.

I was trying to solve it through DAX. Sure helps to think out of the box 🙂

 

Best regards,

Kim

 

 

 

v-xicai
Community Support
Community Support

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:

Is Successor results.jpg

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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