Forum Discussion

UncleCaesar's avatar
UncleCaesar
Regular Visitor
2 years ago

Get Manager ID from Employee Full Name vs. Incomplete Manager Name

Hi guys, 

 

I still bang my head towards this. 

 

I have a CSV that is like this: 

EmployeeID / EmployeeName / ManagerName
1 / John Kenneth von Lichtenstein / Barbara Streisand
2 / Cruela Barbara Streisand del Sol / John Kenneth

3 / Abdul Abi el Camino / John Kenneth

 

etc. 

 

The idea is that the Manager Name column is a short version of the employee's full name, so I'm having a hard time getting the Manager ID from this table. 

 

I'm trying to do a:

IF(Text.Contains[EmployeeName], [ManagerName]), [EmployeeID])

But I'm not sure how to do that in DAX or Power Query... cause it's a sort of XLOOKUP involved per column. 

Any ideas? Or best practices in this case.

Thank you very much for your support!

Need Help

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    not clear about the request. What's the expected output based on the sample data you provided?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi UncleCaesar ,

     

    You can try formula like below:

    ManagerID = 
VAR CurrentEmployeeName = 'YourTable'[EmployeeName]
VAR ManagerName = 'YourTable'[ManagerName]
VAR CurrentEmployeeID = 'YourTable'[EmployeeID]
VAR ManagerID =
    IF (
        ISBLANK ( CurrentEmployeeName ) || ISBLANK ( ManagerName ),
        BLANK (),
        VAR ManagerNameList =
            VALUES ( 'YourTable'[ManagerName] )
        VAR ManagerIDList =
            FILTER ( 'YourTable', CONTAINSSTRING ( CurrentEmployeeName, [ManagerName] ) )
        RETURN
            MAXX ( ManagerIDList, [EmployeeID] )
    )
RETURN
    ManagerID

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • UncleCaesar's avatar
      UncleCaesar
      Regular Visitor
      2 years ago


      This output is what I'm looking for. Thank you!

      For some reason, it doesn't work for me 😞 It is a little buggy, not sure what I might be doing wrong.