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

[UncleCaesar]

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!

[Ashish_Mathur]

Hi,

Show the expected result.

[v-kongfanf-msft]

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]

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. 

[v-kongfanf-msft]

Hi @UncleCaesar ,

You need to create a calculated column instead of a measure, which is probably why you're getting it wrong. Refer to below pbix file.

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]

Hi, looking closely it doesn't work for you either, cause Barbara Streisand has Manager ID "2" instead of "3" and John Kenneth appears correct only on the 2nd row, on the 3rd one it's blank. 

[ryan_mayu]

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