Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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!
Solved! Go to Solution.
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
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.
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.
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
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |