Lookup from same table

[A_Vijgo]

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

[Shailesh2thakur]

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 

[ryan_mayu]

@A_Vijgo 

could you pls provide the expected output in the table as well? That will be easier to understand.

[A_Vijgo]

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.

[ryan_mayu]

sorry, still confused about the explaination. In your sample data ,what's the exactly result we should have?

[A_Vijgo]

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

[ryan_mayu · Accepted solution]

@A_Vijgo 

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

[A_Vijgo]

Hi

Thanks a ton! Exactly what i wanted, worked perfectly.

[ryan_mayu]

you are welcome

[A_Vijgo]

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?

[ryan_mayu]

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.