PowerPivot Hierarchy with multiple parents

[joe321]

Is it possible to create a hierarchy in DAX with more than one parent? I have a table with many business entities with a parent ID. The problem I am having is some entities can be partially owned by multiple parents, For example Entity C can be owned 50% by entity B and 50% by Entity A. Entity C in this case has two parents. I am struggling to find a way to handle this. Everything works well if I exclude the items with multiple parents.

 

[Matt Allington]

The definition of a Hierarchy is 1 to 1 parent/child. You can handle the scenario you describe using a Many to Many pattern. But I warn you it is not for the faint hearted. You can watch a video on the topic here.

Many-to-Many Relationships in DAX - SQLBI

 

[joe321]

Thanks for pointing me in the right direction. I will give it a try and report back.

 

[joe321]

I was finally able to spend some time on this and got the results I needed. Thanks for pointing me in the right direction. The end result worked much better than I could have ever anticipated.

 

[seanhenrici]

Could you please share the method you used to solve this issue?

 

[tommy13]

I have 365 Business premium and I don't have power pivot. Do I have to purchase 365 pro plus? is their any add on's I can buy for excel? Just trying to avoid not setting up a new account.

 

[Matt Allington]

I have 365 Business premium and I don't have power pivot. Do I have to purchase 365 pro plus? is their any add on's I can buy for excel? Just trying to avoid not setting up a new account.

You don't need a new account, just change the product from business premium to Professional. You can change licence at any time. Yes, you can't use Power Pivot if you don't have the software in your SKU. You could use Power BI Desktop however (which is a free download)