Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Solved! Go to Solution.
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
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
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
Thanks. I was already aware of this some time ago but nice to have it logged here : )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |