Is there a way to do lookup with conditions

[Anonymous]

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

[v-kelly-msft]

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!

[Ashish_Mathur]

Hi,

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

[daxer-almighty]

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

[CNENFRNL · Accepted 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"
)

[VijayP]

@Anonymous 

you can use merge option and get Single table