cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Eugenie1989
New Member

Is there a way to do lookup with conditions

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.

 
Table 1: Employee Details                                                     Table 2: Employee

Table 1: Employee DetailsTable 1: Employee DetailsTable 2: EmployeeTable 2: Employee 

 

 

 

 

 

 

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.

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

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"
)

View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

Hi @Eugenie1989 ,

 

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:

v-kelly-msft_0-1623145415949.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Ashish_Mathur
Super User III
Super User III

Hi,

To know the latest status, you must have a Date column in Table1.  Do you have that?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
daxer-almighty
Solution Sage
Solution Sage

@Eugenie1989 

 

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.

CNENFRNL
Super User III
Super User III

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"
)

View solution in original post

VijayP
Super User I
Super User I

@Eugenie1989 

you can use merge option and get Single table 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.