Results 1 to 3 of 3

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

  1. #1
    Neophyte Costin's Avatar
    Join Date
    May 2019
    Excel Version

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

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Excel Version
    Office 365 Pro Plus 1708.8431.
    file keep crashing, but attaching anyway 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 check = 0 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#

  3. #3
    Seeker BernardBrussels's Avatar
    Join Date
    Mar 2019
    Excel Version
    excel 365
    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.
    Last edited by BernardBrussels; 2019-05-24 at 11:34 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts