Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 ?

 

2 ACCEPTED SOLUTIONS

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

Hi @Anonymous,

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-powerbi/

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/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
v-micsh-msft
Employee
Employee

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

 

Anonymous
Not applicable

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

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

Hi @Anonymous,

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-powerbi/

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/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.