Parent Child Hierachy

[Nick_M]

@avalon.ds
I attached the PBIX below. There was some work to do in Power Query, which you can see all the applied steps. But high-level:

1. Start with your main table, and create a column that if ParentEmployee Key = null then EmployeeKey else ParentEmployeeKey. Cant have nulls when using the hierarchy DAX functions. Remove the original Parent column, as we will be using this one

2. Group this table by EmployeeKey, aggregating by Count ( Count All Rows) and then by All Rows

3. Expand this table out

4. Rename the count row to Count of Parent. This is where we start to see what are the repeating values. The only thing we need to account for is that in the steps of #1, we will see a count of two for that row and that’s not what we want. So we write a new custom column that if EmployeeKey = ParentKey then we want 1 otherwise we want that count of parent value

5. Duplicate this query

6. In the original query filter custom column in #4 on = 1

7. Remove all the other columns except EmployeeKey and Parentkey.

8. Be sure to set data types to whole numbers

9. Back in the duplicated query, filter on Custom > 1. Be sure to change that in the formula bar because since the only options are 1 or 2 for that column, if you select “2” from the drop down it will be translated into Custom = 2, which may not always be true.

10. Remove all the other column, set data types, etc…

11. Now, we are going to merge those two tables. After merging, expand out, and then fill down the nulls. I’d rename the columns so you know what is what. But should be pretty clear just by looking at the data.

12. I would disable the load of all the tables except the final Merge Table

Here’s what it should look like:

Now we that all set up ( much easier to see in the applied steps )we can work on using dax and the hierarchy functions.

1. Create the path using EmployeeKey and Parent Key
   Path = PATH(Merge1[EmployeeKey], Merge1[ParentKey])

2. Then using that PATH column we can write a Final Path function:

Final Path = 
IF( 
    AND( 
        //checks to see if the final item in the path matches the employee key from the repeating ParentKey
        PATHITEM([Path],PATHLENGTH( [Path]),INTEGER) = MAX(Merge1[ParentKey from 2]),  

        //Checks to see if the first item in the path matches the repeating Employee Key
        PATHITEM([Path],1,INTEGER) = MAX(Merge1[EmployeeKey from 2])
    ),  
    //If both of the above are true, then combine the current path with the repeating Employee key at the end
    [Path]&"|"&[EmployeeKey from 2],

    //if not, then just want the original path
    [Path]
)

Final table:

PBIX file:
Repeating Hierarchy.pbix (63.4 KB)