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
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?
pls try to modify the dax
LM1 = MAXX(FILTER('Table','Table'[EID]=EARLIER('Table'[Line Manager ID]) && 'Table'[month]=EARLIER('Table'[month])),'Table'[Line Manager ID])
apply the change for all the three columns.