Forum Discussion
Lookup from same table
Hi
I am having trouble with looking up for data from the same table. What i am trying to achieve is to capture line manager hierarachy that rolls up to CEO for that particular month (It is a monthly consolidated table). The table looks something like below. In the LM1 column i want to capture who LM ID 2673 reports to (2673 is also an employee and exists under EID column). What could be the best way to approach this? Please help
| EID | Name | Line Manager ID | Month | LM 1 | LM 2 | LM 3 |
| 1036 | John | 2673 | Oct-23 | |||
| 1178 | Claire | 2876 | Oct-23 | |||
| 1789 | Charles | 2897 | Oct-23 | |||
| 1436 | Sid | 2567 | Oct-23 | |||
| 1098 | Jackie | 2345 | Oct-23 | |||
| 1236 | Syed | 2765 | Oct-23 | |||
| 1897 | Nolan | 2987 | Oct-23 | |||
| 1036 | John | 2567 | Nov-23 | |||
| 1178 | Claire | 2345 | Nov-23 | |||
| 1789 | Charles | 2765 | Nov-23 | |||
| 1436 | Sid | 2987 | Nov-23 | |||
| 1098 | Jackie | 2673 | Nov-23 | |||
| 1236 | Syed | 2876 | Nov-23 | |||
| 1897 | Nolan | 2897 | Nov-23 |
pls try this
LM1 = MAXX(FILTER('Table','Table'[EID]=EARLIER('Table'[Line Manager ID])),'Table'[Line Manager ID]) LM2 = MAXX(FILTER('Table','Table'[EID]=EARLIER('Table'[LM1])),'Table'[Line Manager ID]) LM3 = MAXX(FILTER('Table','Table'[EID]=EARLIER('Table'[LM2])),'Table'[Line Manager ID])pls see the attachment below
10 Replies
Hi
Thanks for the reply. I need, in LM1 column, the Line Manager ID for 2673. 2673 itself is in EID column who has a different Line Manager. and in LM 2 column, the line manager of that line manager.
sorry, still confused about the explaination. In your sample data ,what's the exactly result we should have?
Relationsip (parent and child) function like Path and Path length can be used directly here.
Pls refer the link
https://learn.microsoft.com/en-us/dax/parent-and-child-functions-dax
