Ragged hierarchy measures

DAX DAX Calculations
1

Hierarchy test case.pbix (2.5 MB)

Hello all,

I’m facing an issue with displaying correct data that would meet given conditions.
Working with data from Azure Devops data source with task to present Scrum hierarchy on gantt chart timeline

I’ve managed to expand the parent >> child relations into conditional columns, one for each different work item types, but now facing issue with multiple data available for the highest level of parent as it fetches everything from drill down to lowest hierarchy level.

The best example is to focus on Level 1 of hierarchy named “Dashboard”.
It is an Epic work item type and I would like to create a measure that would lookup for a data where it meets this criteria, so would only result in one applicable created and changed dates. Currently it shows data for every other work item type.
Measure then should do the same for next level of hierarchy with work item type of “Feature”, and so on to the level 1.

2021-11-25 12_46_51-Hierarchy test case - Power BI Desktop
2021-11-25 12_46_51-Hierarchy test case - Power BI Desktop1513×848 64 KB

Appreciate your help :slight_smile:

Attaching demo file

2

@DanDer

Here “Dashboard” is Parent for all workitems. That’s why can see all workitems
Please check the hierarchy

image
image1380×618 75.3 KB

First table you applied filter on WorkitemType, that’s why we can see only Epic

image
image822×640 35.4 KB

3

Thank you Rajesh,

I’m aware of both points you’ve mentioned.
I’ve filtered workitem type for Epic and Feature on purpose to showcase what result I’m looking for.
So that Lev 1 column “Dashboard” with condition of workitem type of “Epic” will give me only one row of data, and then going to level 2 “Discovery”, “Development” etc. will give me data for condition of workitem of “Feature” and so on till level 4.

4

Bumping this post for more visibility.

1 Like
5

Hi @DanDer - Based on my understanding of requirement, it is not possible in a Table visual but in a Matrix visual. Also, workitem will be part of Column instead of Row.

Have created below measure for Changed Date and similarly one for Create Date. Check if it is helpful.

Changed = 

var Lv1 =  SUMMARIZE(filter(Sheet1,Sheet1[Path lenght] = 1),Sheet1[WorkItemType])
var Lv2 =  SUMMARIZE(filter(Sheet1,Sheet1[Path lenght] = 2),Sheet1[WorkItemType])
var Lv3 =  SUMMARIZE(filter(Sheet1,Sheet1[Path lenght] = 3),Sheet1[WorkItemType])
var Lv4 =  SUMMARIZE(filter(Sheet1,Sheet1[Path lenght] = 4),Sheet1[WorkItemType])

return

SWITCH(TRUE(),ISINSCOPE(Sheet1[Lev 1]),if(max(Sheet1[WorkItemType]) = Lv1,max(Sheet1[ChangedDate]),blank()),ISINSCOPE(Sheet1[Lev 2]),if(max(Sheet1[WorkItemType]) = Lv2,max(Sheet1[ChangedDate]),blank()),ISINSCOPE(Sheet1[Lev 3]),if(max(Sheet1[WorkItemType]) = Lv3,max(Sheet1[ChangedDate]),blank()),ISINSCOPE(Sheet1[Lev 4]),if(max(Sheet1[WorkItemType]) = Lv4,calculate(max(Sheet1[ChangedDate])),blank()),BLANK())

image
image

Hierarchy test case.pbix (2.5 MB)

Thanks
Ankit J

6

Hello @DanDer, we noticed no response from you since November 26.

Did the responses above help you with your inquiry?
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.