Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Hope you can help me with lookup query, I am still quite new to Power BI and data analysis:
Problem:
I want to use Employee Name from "Employee Details" as the primary key to do a lookup and populate the Employee Status in the second table "Employee"
However, due to that there are duplicated names exist in "Employee Details" table (Nicole Miller),I am not able to perform the lookup function.
Scenario:
And I also want to clarify that, each row in Table 1 is a unique record. For example, Nicole Miller from Row 5, has Active status from Project Team 1, but Nicole Miller has Inactive status from Project Team 2, 3, 4.
This is actually a valid use case because Nicole Miller has rolled off from Project Team 2-4, she is now on Project Team 1 and she is still an active employee within the company. So if the employee has multiple records in Employee Details table, with both active and inactive Employee Status, the employee is actually still active in the company.
What I want to achieve:
So I was wondering if there is a formula to lookup and populate the right Employee Status in the second table "Employee".
Esp when the same employee has multiple records in "Employee Details", I would want the Employee Status to be Active in "Employee".
Much appreciated for any suggestions for handling this situation, either if there is a way to do a conditional lookup, or create a new column in the first table etcs.
Solved! Go to Solution.
Create a relationship between Employee[Employee Name] (1:*) 'Employee Details'[Employee Name]; then a calculated column is
Employee Status =
IF (
"Active"
IN SELECTCOLUMNS (
RELATEDTABLE ( 'Employee Details' ),
"@status", 'Employee Details'[Employee Status]
),
"Active",
"Inactive"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @Anonymous ,
First create a column in "Employee Details":
New status =
var _count=CALCULATE(COUNTROWS('Employee Details'),FILTER(ALL('Employee Details'),'Employee Details'[Employee Name ]=EARLIER('Employee Details'[Employee Name ])))
Return
IF(_count>1,"ACTIVE",'Employee Details'[Employee Status])
Then create a column in "Employee":
status = LOOKUPVALUE('Employee Details'[New status],'Employee Details'[Employee Name ],'Employee'[Employee Name ],blank())
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi,
To know the latest status, you must have a Date column in Table1. Do you have that?
@Anonymous
A word of warning: You should not rely on names but employee numbers. So, if you have to, then please do not join tables on emp names (because one day you will have very big eyes) but on the unique identifiers of the employees. If you don't want to join the tables, then there's also a way to get what you want. However, I think Power Query is a more relevant tool for your requirement. Calculated columns should be the last resort when there's no other way to obtain the result. Calculated columns do not participate in the compression phase and therefore tables with such columns are not fully optimized for speed.
Create a relationship between Employee[Employee Name] (1:*) 'Employee Details'[Employee Name]; then a calculated column is
Employee Status =
IF (
"Active"
IN SELECTCOLUMNS (
RELATEDTABLE ( 'Employee Details' ),
"@status", 'Employee Details'[Employee Status]
),
"Active",
"Inactive"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous
you can use merge option and get Single table
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |