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