Reply
Member
Posts: 58
Registered: ‎11-10-2015
Accepted Solution

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.

 

Capture.PNG

 

 

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

Capture.PNG

 

 

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

View solution in original post

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

Re: Parent-Child hiearchy handling with multiple Parents

Hi @danielhjorvar,

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/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post


All Replies
Super User
Posts: 10,462
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.


Did I answer your question? Mark my post as a solution!

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.

29.PNG

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

Hi @danielhjorvar,

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/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




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 : )