Creating a Parent-Child Hierarchy

[Anonymous]

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 manager from the ID column. Desired result:

ID	Name	Manager	ManagerID
1	Adam	John	2
2	John

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

[vivran22]

@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

[v-lionel-msft]

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]

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:

ID	Name	Manager	Manager ID
1	Adam	John	2
2	John	John	2

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

Thanks

[v-lionel-msft · Accepted solution]

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]

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]

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:

ID	Name	Manager	Manager ID
1	Adam	John	2
2	John

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

Thanks

[Greg_Deckler]

@Anonymous  - You can use LOOKUPVALUE for that. Or follow the Lookup Min/Max pattern. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

Also, if you create a concatenated column with a | character between then you could just use PATH

https://docs.microsoft.com/en-us/dax/path-function-dax

[Anonymous]

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