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 ,

 

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