Issues Using PATH Function with Multiple Root Nodes in Product Hierarchy

[TomerIwanir1]

Hi everyone,

I'm working with a product hierarchy in Power BI using the PATH function to model parent-child relationships. The challenge I'm facing is that I have multiple root-level products (i.e., products that don't have a parent).

Since PATH requires each node to also appear as a child, Copilot suggested I add these root products as children with a dummy parent (e.g., blank or null) and a quantity of 1. However, after doing this, I get an error saying that the value "" (blank) appears multiple times and cannot be parsed.

Has anyone dealt with a similar situation?
How do you handle multiple root nodes in a hierarchy when using PATH?
Are there best practices or workarounds to make this work without causing parsing issues?

Thanks in advance!

[v-tsaipranay]

Hi @TomerIwanir1 ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay]

Hi @TomerIwanir1 ,

I appreciate the clarification. Based on your update, the error you're encountering indicates that the product 424000050 is associated with multiple parent nodes, meaning your data represents a many-to-many relationship rather than a strict parent-child hierarchy.

The PATH() function in DAX is specifically designed for strict hierarchies, where each child has only one direct parent. When a single node (like 424000050) has multiple parents, PATH() cannot determine a single lineage and thus fails with the error you're seeing.

To resolve this, If your reporting scenario allows, consider introducing a primary parent for such products to enforce a single-parent structure, making it compatible with PATH().

If retaining all parent relationships is critical, you may need to move away from PATH() and instead consider modeling the hierarchy using a bridge table or exploring custom DAX logic to traverse relationships manually. Alternatively, network graph visuals or external processing tools may better suit this kind of structure.

Hope this clear your issue. Please feel free to reach out, if you need any further assistance

Thank you.

[TomerIwanir1]

Hi @DataNinja777 , 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?

[v-tsaipranay]

Hi @TomerIwanir1 ,

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.

[TomerIwanir1]

Hi @v-tsaipranay  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?

[DataNinja777]

Hi @TomerIwanir1 ,

This is a very common scenario when working with parent-child hierarchies in Power BI. The PATH function fails when multiple rows have a blank or null parent because it cannot resolve a single, unambiguous starting point for the hierarchy. The error you're seeing confirms that DAX found multiple roots and couldn't proceed.

The best practice to solve this is to create a single virtual root node. Instead of having multiple products with no parent, you'll modify your data so that all of your top-level products report to one new, artificial parent. This gives the PATH function a single, clear starting point for all branches of your hierarchy. The most robust place to make this change is in the Power Query Editor before the data is loaded into the model.

First, you would identify all rows where the parent ID is null. Then, you would replace those nulls with a new, consistent identifier, such as "VirtualRoot". Finally, you must add a new row to your table for this virtual parent itself. This new row would have "VirtualRoot" as its own ID and its parent ID would be blank. For example, you can use Table.ReplaceValue to update the parent column for the existing roots.

= Table.ReplaceValue(#"Previous Step", null, "VirtualRoot", Replacer.ReplaceValue, {"ParentID"})

After replacing the nulls, you would append a new row to your table, for instance: [ProductID="VirtualRoot", ProductName="All Products", ParentID=null]. Once this is done, only the single virtual root will have a blank parent, and your PATH function will work as expected.

Alternatively, if you cannot edit the query, you can achieve the same result using a DAX calculated table. You would create a new table that unions your original data with a new row for the virtual root, and then uses a function like IF to reassign the parent for the original root nodes.

Hierarchy_Fixed = 
VAR OriginalRootsUpdated =
    ADDCOLUMNS(
        FILTER('ProductHierarchy', ISBLANK([ParentID])),
        "NewParentID", "VirtualRoot"
    )
VAR NonRootProducts =
    ADDCOLUMNS(
        FILTER('ProductHierarchy', NOT ISBLANK([ParentID])),
        "NewParentID", [ParentID]
    )
VAR VirtualRoot = 
    ROW("ProductID", "VirtualRoot", "ProductName", "All Products", "NewParentID", BLANK())
RETURN
    UNION(OriginalRootsUpdated, NonRootProducts, VirtualRoot)

After creating this corrected table, you can build your PATH column on it without encountering the parsing error.

Best regards,