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, Empo...
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)
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.

     





    • Bmejia's avatar
      Bmejia
      Super User
      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)
      • IoannisT's avatar
        IoannisT
        Advocate I
        2 years ago

        You are a legend!

        Kudos and accepted solution.