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: ID Name Manager 1 Adam John 2 John     I want there to be another column called 'Manager ID' that obtains the ID of each person's...
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.

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.