Forum Discussion

IoannisT's avatar
IoannisT
Advocate I
2 years ago
Solved

Same table TEXT lookup

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

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

 

 

 



Need Help
  • Bmejia's avatar
    Bmejia
    2 years ago

    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)

5 Replies

  • Bmejia's avatar
    Bmejia
    Super User
    2 years ago

    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's avatar
      IoannisT
      Advocate I
      2 years ago

      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's avatar
        Bmejia
        Super User
        2 years ago

        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.