topic Re: Creating a Parent-Child Hierarchy in Developer

Creating a Parent-Child Hierarchy

Anonymous — Tue, 04 Aug 2020 15:38:54 GMT

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.

Re: Creating a Parent-Child Hierarchy

Greg_Deckler — Tue, 04 Aug 2020 16:13:09 GMT

@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

Re: Creating a Parent-Child Hierarchy

v-lionel-msft — Wed, 05 Aug 2020 07:57:17 GMT

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.

Re: Creating a Parent-Child Hierarchy

Anonymous — Wed, 05 Aug 2020 08:29:35 GMT

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

Re: Creating a Parent-Child Hierarchy

Anonymous — Wed, 05 Aug 2020 08:32:08 GMT

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

Re: Creating a Parent-Child Hierarchy

v-lionel-msft — Wed, 05 Aug 2020 11:06:46 GMT

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.

Re: Creating a Parent-Child Hierarchy

Anonymous — Wed, 05 Aug 2020 12:01:27 GMT

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

Re: Creating a Parent-Child Hierarchy

vivran22 — Tue, 11 Aug 2020 04:25:03 GMT

@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

Re: Creating a Parent-Child Hierarchy

v-lionel-msft — Tue, 11 Aug 2020 10:41:12 GMT

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.