Same table TEXT lookup

[IoannisT]

Hello Fabric Community.

I have searched over the forum (and other sources), but I am unable to make a same table TEXT lookup function to work.

I have a raw data table with Employee name, Empoyee ID and manager ID and I want to create columns with up to 7 levels down of reporties.

This is what the data looks like vs what I want to built:

Employee Name	E-ID	Manager-ID
Andrew Anderson	a1a34	
Mary Jane	m2j23	a1a34
Peter Parker	p3p45	a1a34
Jim Smith	j4s43	m2j23
Jim Johnson	j3j98	m2j23
Katy Sons	k8s45	j4s43
Mark Magnuson	m2m65	k8s45

I have tried 

L2_manager-ID =
IF(ISBLANK([#"Table"[#"Manager-ID"],
"",
LOOKUPVALUE(#"Table"[E-ID],
#"Table"[Employee Name],
#"Table"[#"Manager-ID"]))
------------------------------------------
L3_manager-ID =
IF(ISBLANK([#"Table"[#"L2_manager-ID"],
"",
LOOKUPVALUE(#"Table"[E-ID],
#"Table"[Employee Name],
#"Table"[#"L2_manager-ID"]))
------------------------------------------
L4_manager-ID =
IF(ISBLANK([#"Table"[#"L3_manager-ID"],
"",
LOOKUPVALUE(#"Table"[E-ID],
#"Table"[Employee Name],
#"Table"[#"L3_manager-ID"]))

...............................

[Bmejia]

Instead of lookup value use PATHITEMREVERSE with PATH.  

Create a new column as follow for L2-Manager then another one for L3-Manager changing the value 3 to 4.

L2-Manager = PATHITEMREVERSE(PATH(PathTable[E-ID],PathTable[Manager-ID]),3,TEXT)

[IoannisT]

Hi Bmejia

Unfortunately this will not work as the database does not have correct hierarcky all around. What I mean by that is that some computers have their own account and their manage is -null.

Also some servers have a manager attached to them but the manager has left the company and noone has changed them to the new person.

Is there any other way you can think of?

* I have tried your DAX code and I got an error that an ID must be both in employee and manager (this id was not the CEO)

[Bmejia]

I don't know if this would work, since the value is null then you can replace the null value with the  E-ID, go into transformation and add a conditional column. Then use this column instead as your manager column.

As for the other concern It think regarding data not being updated.  That would be a data management issue, which you can't control would probably would still get the same wrong data in your results.

[Bmejia · Accepted solution]

if you really need to use lookupvalue it should be something like this. 

L2 Manager =

VAR L2M = LOOKUPVALUE(PathTable[Manager-ID],PathTable[E-ID],PathTable[Manager-ID])

RETURN

IF(L2M=PathTable[Manager-ID],BLANK(),L2M)
-------------------------------------------------------

L3 Manager =

VAR L3M = LOOKUPVALUE(PathTable[L2 Manager],PathTable[E-ID],PathTable[Manager-ID])

RETURN

IF(L3M=PathTable[Manager-ID],BLANK(),L3M)

[IoannisT]

You are a legend!

Kudos and accepted solution.