Parent-child hierarchy with changing number of levels and possibility to filter by any level

[bigk]

Hello

I want to report data on task owner and multiple levels of task owner's managers. The end result would be a slicer where i can select any level of manager and get all tasks for the manager and his/her subordinates. This should be possible to use in both hierarhial structure, e.g. with drilldown and flat filtering, e.g. when filter contains list of all people in org and when selecting a specific person, all his/her tasks and subordinate tasks are filtered.

This is pretty much typical question and a lot of solutions can be found online. But the problem is that most of them are calculating fixed number of levels using PATHITEM expression. I don't want to limit my data to specific number of path items because the number of levels may grow over time and i don't want to monitor and change query all the time. I've found a video (Managing hierarchical organizations in Power BI security roles (youtube.com)) that almost solves my issue but the difference there is that the Permissions table in the video contains all managers for specific user in individual rows. While i have only one child-parent combination per row

Contacts table

Person	Manager	Org Hierarchy ( = PATH(Contacts[Person],Contacts[Manager]))
John	Mike	Mike|John
Mike	Anna	Anna|Mike|John
Anna	Michael	Michael|Anna|Mike|John
Joseph	Anna	Michael|Anna|Joseph
Michele	Michael	Michael|Michele
Claudia	null	Claudia
Michael	null	Michael

End result should be:

• Filter by Michael would return tasks for Michael, Anna, Mike, John
• Filter by Anna would return tasks for Anna, Joseph, Mike, John

When following video, i've made following table:

ManagerReport=

GENERATE(

        SELECTCOLUMNS(

        Contacts,

        "Person", Contacts[Person],

        "Manager", Contacts[Manager]

        ),

        VAR _AssignedManager = [Manager]

        RETURN

        CALCULATETABLE(

            DISTINCT(Contacts[Org Hierarchy]),

            PATHCONTAINS(Contacts[Org Hierarchy],_AssignedManager),

            REMOVEFILTERS()

        )

)

Results are:

As you can see, i'm getting only closest level parent name instead of all managers in the hierarchy. For example, all owners should have rows where Michael is manager, but only Anna and Michele have that as Michael is closest manager.

Thanks for looking into this.

[Anonymous · Accepted solution]

Hi @bigk ,

Thanks lbendlin  for the quick reply. I have some other thoughts to add:

(1) My test data is the same as yours.

(2) Create two columns.

Org Hierarchy = PATH(Contacts[Person],Contacts[Manager])

Column = PATHLENGTH([Org Hierarchy])

(3) Create a table.

Table =
VAR table1 =
    GENERATESERIES ( 1, MAX ( Contacts[Column] ), 1 )
VAR table2 =
    ADDCOLUMNS (
        GENERATEALL ( VALUES ( Contacts[Person] ), table1 ),
        "Manager",
            VAR a =
                MAXX (
                    FILTER ( Contacts, [Person] = EARLIER ( Contacts[Person] ) ),
                    [Org Hierarchy]
                )
            RETURN
                PATHITEM ( a, [Value] )
    )
RETURN
    FILTER ( table2, [Manager] <> BLANK () && [Person] <> [Manager] )

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[bigk]

Hi @Anonymous 

This was exactly what i needed. Thanks! I've slightly modified it for my needs and removed 

[Person] <> [Manager] 

from the expression becuase i would like to use the same field for both task owner (Person) and manager. And in case if Person does not have manager, then this person will be excluded from the field.

Meanwhile i've found other solution. I've used this function Dynamically solve parent child hierarchies in Power BI and Power Query (youtube.com) to dynamically calculate the person-manager table in PQ. But i like your solution better as it minimize number of frictions in PQ. 

[bigk]

Hi. Thanks for reply. However i did not get your suggestion. Currently i don't have any query except the one that is listed above. PATHITEM was referred as a solution that does not fit me because it is used to split hierarchy to multiple columns which is not the solution i'm looking for. And the query above actually already contains PATHCONTAINS.

[lbendlin]

instead of PATHITEM use PATHCONTAINS.  Of course that will fail if two people have the same name.