## Desktop

Highlighted
Member
Posts: 58
Registered: ‎11-10-2015

# Parent-Child hiearchy handling with multiple Parents

The classic Parent-Child hierarchy is quite trivial these days with very thorough explanations to be found from multiple sources online.

However, when it comes to multiple parent-child hierarchy... Well, lets just say I'm spending way too much time on that one.

Has anyone here solved this problem with DAX ? If so, I'm curious on how you did so and would be grateful for the solution.

First picture shows the normal Parent-Child hiearchy. I'm using a simple work-diagram to show those who are bosses over employees.

Now, if, for example Sharon has two bosses.... we have a problem. We have that "multiple" parent dilemma as shown in the next figure...

What's the most ideal, most efficient way to handle this with DAX ?

Accepted Solutions
New Contributor
Posts: 570
Registered: ‎08-11-2016

## Re: Parent-Child hiearchy handling with multiple Parents

Hi Danielhiorvar,

Would it help to create a table for the Head account (Level 2) and the total (Level 3)?

For those who have two totals, just create two records for them. For Example, we now have a table with Head Account and the Total.

One Column for Head Account and one for Total.

 Head Account Total 1 A 2 B 2 A 3 C

....

Head Account 2 have two Totals,  then create two record for it. After that, create relationship for Head Account and the Total for other reousrce tables.

Regards

Super User
Posts: 1,620
Registered: ‎09-06-2015

## Re: Parent-Child hiearchy handling with multiple Parents

you can solve the multi-parent-hierarchies with this approach as well: http://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-pow...

A typical example with multi-parent-hierarchies in action is this: http://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi/

Proud to be a Datanaut!

Imke Feldmann

All Replies
Super User
Posts: 10,508
Registered: ‎07-11-2015

## Re: Parent-Child hiearchy handling with multiple Parents

Well, in your data, Sharon would have two rows of information while everyone else would have one row. Sharon would have one with Jill as her boss and one with Simon as her boss. Relationships would still work just fine. I think this is more of a data structure issue than a DAX relationship issue unless I am missing something.

Proud to be a Datanaut!

New Contributor
Posts: 570
Registered: ‎08-11-2016

## Re: Parent-Child hiearchy handling with multiple Parents

Hi danielhjorvar,

In Power BI we choose to manage the fact table, and build the relationships between different tables. Actually Power BI deals with the actual data, which is used to build reports.

I think the hierarchy you mentioned here could be accompulished by creating hierarchy in Report View, if this hierarchy is needed in reports building.

Right click on the item in the Fields pane, we could see there is New Hierarchy available.

In addition, we could manage table relationship in Relationship View in Power BI desktop, check the article below for more details:

Create and manage relationships in Power BI Desktop

If the hierarchy here is used for data access, then you may take a look at the Role Level Security in Power BI:

Row-level security (RLS) with Power BI

Please reply back if you need any further assistance on this topic.

Regards

Member
Posts: 58
Registered: ‎11-10-2015

## Re: Parent-Child hiearchy handling with multiple Parents

Thanks for the replies @Greg_Deckler & @v-micsh-msft

What I'm dealing with is that I have a unique set of sub-accounts (Level 1). Each one of those account belong to head-account (Level 2) and one head account only. For this dilemma I've used hierarchy with good results.

However.

The head-accounts can belong to one or more Totals (Level 3) where multi-parent hierarchy scenario steps in.

Maybe I'm overthinking this but I simply cannot create this multi-parent hierarchy between the head-accounts and Totals

New Contributor
Posts: 570
Registered: ‎08-11-2016

## Re: Parent-Child hiearchy handling with multiple Parents

Hi Danielhiorvar,

Would it help to create a table for the Head account (Level 2) and the total (Level 3)?

For those who have two totals, just create two records for them. For Example, we now have a table with Head Account and the Total.

One Column for Head Account and one for Total.

 Head Account Total 1 A 2 B 2 A 3 C

....

Head Account 2 have two Totals,  then create two record for it. After that, create relationship for Head Account and the Total for other reousrce tables.

Regards

Super User
Posts: 1,620
Registered: ‎09-06-2015

## Re: Parent-Child hiearchy handling with multiple Parents

you can solve the multi-parent-hierarchies with this approach as well: http://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-pow...

A typical example with multi-parent-hierarchies in action is this: http://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi/

Proud to be a Datanaut!

Imke Feldmann

Member
Posts: 58
Registered: ‎11-10-2015

## Re: Parent-Child hiearchy handling with multiple Parents

Thanks. I was already aware of this some time ago but nice to have it logged here : )