Issues Using PATH Function with Multiple Root Nodes in Product Hierarchy

Hi Anonymous ,

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,