Lookup value from different row in same output column - circular reference error

[Costin]

I have a solution that works fine in Excel spreadsheet, but encounters circular reference error in both Power Query M and BI DAX. Need help please.

Let's say I have 2 columns:

Column A - Employee ID
Column B - Manager ID

I'm trying to create a 3rd column [Hierarchy] level with the CEO defined as 0 and each level below him +1.

In an Excel table, this works for me:
= IF ( [Employee ID] = (CEO's ID), 0,
IF ( INDEX( [Hierarchy], MATCH( @[Manager ID], [Employee ID], 0)) +1 )

However if I try to replicate this in Power Query or DAX measures it returns with circular reference error.

I searched the forums for circular reference solutions. There was a solution to create 2 index columns, one starting with 0 and another starting with 1 then merge and calculate. However this only satisfies one pass, and is a non iterative solution. It won't work unless we know exactly how many hierarchy levels, and even then need to duplicate the series of steps for each pass, which seems very cumbersome.

Any genius able to help please?

 

[horseyride]

file keep crashing, but attaching anyway View attachment ff.xlsx

If the two columns were Emp and Boss in Table1 with the boss denoted by ID=0, then create function called depth

(check) =>
let Source=#"Table1",
#"Boss" = Table.SelectRows(Source, each ([Emp]=check)),
Boss2 = #"Boss"{0}[Boss],
#"Look" = if [B]check = 0[/B] then 0 else @depth(Boss2) +1
in #"Look"

and add it to your table with

#"Step" = Table.AddColumn(Source,"Depth", each depth([Emp]))

change the check=0 part so that the zero is the top boss's ID#

 

[BernardBrussels]

Hi ,


I have created such a concept for a french forum.

the question was to create the genealogy of dogs...the concept was more complex as each dog has a mother and a father ...

first create a query lets call it Q_employee

then you copy it as a reference lets call it Q_Emp_tree

then you merge Q_employee into Q_emp_tree and you repeat the operation several time depending on the number of levels you want to achieve.

https://www.filehosting.org/file/details/799452/LOF chiens 2.xlsx