Forum Discussion
Solved
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 mont...
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
Hi
This is the table, columns LM1, LM2 and LM3 is the result columns i need to capture.
| EID | Name | Line Manager ID | Month | LM 1 | LM 2 | LM 3 |
| 1036 | John | 2673 | Oct-23 | 2560 | 2451 | 2981 |
| 2673 | William | 2560 | Oct-23 | 2451 | 2981 | |
| 2560 | Seth | 2451 | Oct-23 | 2981 | ||
| 2451 | Caroline | 2981 | Oct-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
Hi
Thanks a ton! Exactly what i wanted, worked perfectly.
you are welcome
Hi
This seems to work only when there is one month data in it, when i append the next month data it takes the first instance but not for that particular month. For example, when i add the next month's data William could report to someone else and under LM1 for field for next month it needs to capture the line manager for that particular month - How can i tackle that?