Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating a Parent-Child Hierarchy

I have a data set that looks like the following:

IDNameManager
1AdamJohn
2John 

 

I want there to be another column called 'Manager ID' that obtains the ID of each person's manager from the ID column. Desired result:

IDNameManagerManagerID
1AdamJohn2
2John  

I'm not sure how to achieve this. Thanks for your help.

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Please refer to my .pbix file.

    I'm not sure if you want calculated column or measure, so both are created.

    In addition, [__Maganer] is a necessary condition for calculation. Although you don't need it, the final result can not be obtained without it.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg_Deckler

       

      I've tried LOOKUPVALUE but that doesn't seem to achieve what I required - unless I am using it incorrectly?

       

      In terms of the concentrated column, would that be ID|Display Name to use PATH to pull the ID of each person's manager?

       

      Thanks

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Create a calculated column like this.

    Column = 
VAR x =  
CALCULATE(
    MAX(Sheet3[ Name Manager]),
    FILTER(
        Sheet3,
        Sheet3[ID] = EARLIER(Sheet3[ID]) + 1 && MOD( EARLIER(Sheet3[ID]), 2 ) <> 0
    )
)
VAR y = 
IF(
    x = BLANK(),
    " ", x
)
RETURN
IF(
    CONTAINSSTRING(Sheet3[ Name Manager],y),
    Sheet3[ID]+1, BLANK()
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lionel-msft,

       

      I'm not sure if your solution is applicable as I believe you have misunderstood my table (which isn't clear to be fair!) as Name and Manager are separate columns:

       

      IDNameManagerManager ID
      1AdamJohn2
      2John  

       

      What I have entered in red in the desired result I need to achieve.

       

      Thanks

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Please do like this.

    Create two calculated columns.

    (You can also combine these two formulas.)

    __Manager = 
IF(
    [Manager] = BLANK(), 
    [Name], [Manager]
)
    Column = 
VAR x = 
CALCULATE(
    MAX(Sheet4[ID]),
    FILTER(
        Sheet4,
        Sheet4[__Manager] = EARLIER(Sheet4[__Manager]) && Sheet4[Manager] = BLANK()
    )
)
RETURN
IF(
    [Manager] = BLANK(),
    BLANK(), x
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lionel-msft 

       

      Thanks for getting back to me. I don't need that additonal '___ Manager' column and due to that, the formula doesn't work. Any gaps where someone is the top of the hierarchy will be like this:

       

      IDNameManagerManager ID
      1AdamJohn2
      2JohnJohn2

       

      But I can't seem to get your second calculated column to work. Any ideas?

       

      Thanks

      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Please refer to my .pbix file.

        I'm not sure if you want calculated column or measure, so both are created.

        In addition, [__Maganer] is a necessary condition for calculation. Although you don't need it, the final result can not be obtained without it.

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    Anonymous 

     

    You may use Power Query for such scenarios,

     

    Refer to the following article for more details:

    https://www.vivran.in/post/power-query-quickbytes-1-self-join

     

    Hope this helps.

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
    If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

    Blog: vivran.in/my-blog
    Connect on LinkedIn
    Follow on Twitter