Filter by top level of parent child hierarchy

[MateoS]

Hello everyone,

I have some serious obstacle to overcome in Power BI report. My task is to prepare report based on Azure DevOps data. One of requirements for my raport is to allow to filter by hierarchy level. To be specific - there are multiple Workitems in AzureDevOps and a bunch of people responsible for them. Hierarchy is made in accordance with this tutorial and works fine. Problem is to enable filtering by person in hierarchy. For example, there are 4 people assigned to Workitems in Azure DevOps: John, Matt, Frank and Jeff. We can see them on different levels of hierarchy, depending on Workitem in AzureDevOps. Sometimes Matt is on 1st level, sometimes on 4th level and so on. I need to present hierarchical workitems in matrix visual in Power BI with information about responsible person. I made a measure:

Responsible = 
VAR EntityShowRow = [_WorkItemsBrowseDepth_] <=  [_MaxHierarchyDepth_]
VAR LvlVal =
    SWITCH([_WorkItemsBrowseDepth_]
        ,1, IF(MIN(AllWorkItems[Responsible1]) = BLANK(), MIN(AllWorkItems[Responsible2]),MIN(AllWorkItems[Responsible1]))
        ,2, IF(MIN(AllWorkItems[Responsible2]) = BLANK(), MIN(AllWorkItems[Responsible3]),MIN(AllWorkItems[Responsible2]))
        ,3, IF(MIN(AllWorkItems[Responsible3]) = BLANK(), MIN(AllWorkItems[Responsible4]),MIN(AllWorkItems[Responsible3]))
        ,4, IF(MIN(AllWorkItems[Responsible4]) = BLANK(), MIN(AllWorkItems[Responsible5]),MIN(AllWorkItems[Responsible4]))
        ,5, IF(MIN(AllWorkItems[Responsible5]) = BLANK(), MIN(AllWorkItems[Responsible6]),MIN(AllWorkItems[Responsible5]))
        ,6, IF(MIN(AllWorkItems[Responsible6]) = BLANK(), MIN(AllWorkItems[Responsible7]),MIN(AllWorkItems[Responsible6]))
        ,7, IF(MIN(AllWorkItems[Responsible7]) = BLANK(), MIN(AllWorkItems[Responsible8]),MIN(AllWorkItems[Responsible7]))
        ,8, IF(MIN(AllWorkItems[Responsible8]) = BLANK(), MIN(AllWorkItems[Responsible9]),MIN(AllWorkItems[Responsible8]))
        ,9, IF(MIN(AllWorkItems[Responsible9]) = BLANK(), MIN(AllWorkItems[Responsible10]),MIN(AllWorkItems[Responsible9]))
        ,10, IF(MIN(AllWorkItems[Responsible10]) = BLANK(), MIN(AllWorkItems[Responsible10]),MIN(AllWorkItems[Responsible10]))
        ,MIN(AllWorkItems[Responsible1])
    )
RETURN IF(EntityShowRow = TRUE,LvlVal)

A little explaination for code above: AllWorkItems[ResponsibleX] is a calculated column with code as follows:

ResponsibleX = 
VAR LevelNumber = X
VAR LevelKey = PATHITEM ( 'AllWorkItems'[Path], LevelNumber, TEXT )
VAR LevelName = LOOKUPVALUE ( AllWorkItems[Responsible], 'AllWorkItems'[Project Work Item ID], LevelKey )
VAR Result = LevelName
RETURN
    Result

 VAR _WorkItemsBrowseDepth_ is EntityBrowseDepth 

VAR _MaxHierarchyDepth_ is EntityRowDepth 

This code works fine but if I put a slicer or hierarchy slicer in my Power BI report it doesn't work fine. My desired result is to see all workitems assigned to each person and everything below. For instance:

Level 1: Mat

Level 2: John

Level 3: Frank

Level 4: Jeff

If I chose John I want to see his workitems and worktimes assigned to Frank and Jeff. If I chose Frank I want to see his and Jeff's workitem in this hierarchy.

I have no idea how to configure filters or slicers to achieve this result.

Regards

Matt

[v-yalanwu-msft]

Hi, @MateoS ;

Can you post sample data as text and expected output?

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.