I’ve written about a method to dynamically flatten parent-child-hierarchies also with multiple parents some while ago here. I’ve actually used this approach for Bill-of-materials cases and refined that approach in a series starting here. There, the quantities are aggregated in M already, as they are not supposed to change. But if one wants to use the hierarchical structure to report on transaction tables where several filters shall be applied, one has to adjust the data model a bit:
DimNodes
If you have parent-child-hierarchy with multiple parents, my function will a table like below, where the children with multiple parents still reside in different rows:
Due to this, the table cannot directly be connected with the FactTable, as NodeKey is not unique. Solution is to create DimNode-table that contains only unique values from the NodeKeys. Use it as a bridge between the 2 tables and implement a bidirectional filter to the Nodes-table:
Best to hide that table from view, because the fields for the matrix visual have to come from the original tables. Now look at this:
Doesn’t that look like the perfect ragged parent-child-hierarchy-matrix?
Of course, the totals won’t add up, but that’s by design if you allocate items to multiple parents.
All measures are taken from the Russo/Ferrari Parent-Child-Pattern. My M-function produces the same structure than the table created by DAX-functions in that article. Just that it does it dynamically, so you don’t have to add new levels manually and copes with multiple parents. Sure, the dynamic aspect is useless, if you want to create a traditional hierarchy like described in this post, but for Bill-of-materials-solutions, where you aggregate the values in Power Query already, that’s pretty useful actually.
File to download: Parent Child Hierarchy Multiple Parents
Enjoy and stay queryious 😉
Note: The code of the M-function is fairly old an quite embarrassing, but as it works, an update doesn’t get a high priority currently 😉
This is a great article. Thank you. I almost thought this solution would work for me but I still have a challenge that’s slightly different. My (2) parent hierarchy tables have common employees, sometimes, but different leadership hierarchy. They link to my fact table though with 2 different keys so that Parent1 has has its own distinct hierarchy and links to Child with PKey1 and Parent2 has its own distinct hierarchy and links to Child with PKey2. My end goal is to have a single filter that would allow users to say, ‘if i appear in either hierarchy tree, and have ownership to this project, I want to see those projects and who I tier down/up to.
Hi Nathan,
that sounds doable through data modelling. Connect a Users table to both hierarchy-tables and filter the current user on that.
If one table becomes empty, because one condition is not met, the fact table will return empty.
/Imke
Hi, there, what a great tutorial. I would like to know is there a way to aggregate one of the fields(e.g. sales) and don’t aggregate on other fields(e.g. personal salary). I mean how to define which fields should be aggregate and which field should not? Thanks again.
This is a wonderful article! Thank you! 🙂
I have identified about 75 rows of my data that are causing the query to not finish loading. Is there anything i can to stop the Query from stalling?