Parent/child hierarchy creation

[jf955]

Hitting a roadblock on how best to incorporate this into PowerBI. I need to create a multilevelled heirarchy of dashboard info, which goes about 14 levels deep. I have a source table with every child source account. What is the best way to teach PowerBI the heirarchy these source child accounts form, and which parent they belong to (parent accounts aren't in the source data table, and the only way OI have fiogured out how to create them is to add a new column in powerBI and create a sum formula). But as there are close to 1000 accounts needed, this would take too long.

Final result I want to achieve is to have a visual showing the top line summaries, but you can then click into each top line to go one line deeper, and deeper etc as many times as you want.

Any suggestions on how to proceed?

[Akash_Varuna · Accepted solution]

Hi @jf955 ,You could use PATH function for this You’ll need to structure your data to represent parent-child relationships effectively
Example:

• Build Hierarchy Using DAX:

  • Use PATH to generate hierarchy paths:
    Path = PATH(Source[AccountID], Source[ParentID])
  • Extract levels with PATHITEM:
    Level1 = PATHITEM(Source[Path], 1, INTEGER) Level2 = PATHITEM(Source[Path], 2, INTEGER)

• Create a Hierarchy:

  • Drag the Level columns into a hierarchy in the Fields pane.
    Please replace the column names after structuring your table
    If this post helped please do give a kudos and accept this as a solution 
    Thanks In Advance

[v-csrikanth]

Hi @jf955 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth

[v-csrikanth]

Hi @jf955 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth

[v-csrikanth]

Hi @jf955 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth

[v-csrikanth]

Hi @jf955 
Thank you for being part of the Microsoft Fabric Community.
As highlighted by @Akash_Varuna the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth.

[Akash_Varuna · Accepted solution]

Hi @jf955 ,You could use PATH function for this You’ll need to structure your data to represent parent-child relationships effectively
Example:

• Build Hierarchy Using DAX:

  • Use PATH to generate hierarchy paths:
    Path = PATH(Source[AccountID], Source[ParentID])
  • Extract levels with PATHITEM:
    Level1 = PATHITEM(Source[Path], 1, INTEGER) Level2 = PATHITEM(Source[Path], 2, INTEGER)

• Create a Hierarchy:

  • Drag the Level columns into a hierarchy in the Fields pane.
    Please replace the column names after structuring your table
    If this post helped please do give a kudos and accept this as a solution 
    Thanks In Advance

[jf955]

Thanks. Still struggling with this in terms of integrating the 2 tables together after creating the path column. The source table has the accounts as columns rather than rows. How do I link the heirsrchy table to the data table?

[Akash_Varuna · Accepted solution]

Hi @jf955 Since there are paths as columns you will need to unpivot them please try these

• Unpivot the source data in Power Query to transform account names into rows.
• Create a parent-child hierarchy table with AccountID and ParentID columns.
• Use DAX to build the hierarchy:

  • Path = PATH(HierarchyTable[AccountID], HierarchyTable[ParentID])

  • Extract levels with PATHITEM for Level1, Level2, etc.
• Link the hierarchy table to the unpivoted data table using AccountID.
• Drag hierarchy levels (Level1, Level2, etc.) into a Matrix or Tree visual to enable drill-down.