Forum Discussion
Issues Using PATH Function with Multiple Root Nodes in Product Hierarchy
Hi Anonymous ,
Thanks for confirming. You're correct, since PATH() requires a strict one-parent-per-child hierarchy, it won’t work for scenarios like yours with many-to-many relationships. Exploring alternatives like bridge tables or custom logic is the right direction.
Thank you.
Hi Anonymous ,
Thank you for reaching out to the Microsoft Fabric Community Forum, and thank you DataNinja777 for your explanation which is indeed correct.
The error you're encountering is due to multiple root-level nodes (blank parents), which the PATH() function cannot process as it expects a single, clear root. To resolve this, the best practice is to introduce a single virtual root node (e.g., "VirtualRoot") and update all existing root items to use this as their parent.
You can implement this either in Power Query by replacing nulls and adding a virtual root row or using a DAX-calculated table that restructures the hierarchy accordingly. This approach ensures PATH() parses the hierarchy without ambiguity or errors.
I hope this will resolve your issue, if you need any further assistance, feel free to reach out.
Thank you.
Hi Anonymous thx ! I tried to do it but I got the following error :
"Each value in 'Final to path'[ps_comp] must have the same value in 'Final to path'[ps_par]. The value '424000050' has multiple values."
the value 424000050 indeed has 5 parents, its a product that can be a part of many products, does it means I cant work with PATH?