Trying to work out how to count all items with same parent feature as those in current sprint(s)

[mbowler]

I am extracting data from our Azure DevOps system that is managing all the work items we work on in an Agile sprint fashion. The report I want will be using daily refreshed data. An example of the data is below:

Id	Title	Type	State	Sprint	InCurrSprint	Feature
100	Old but relevant	PBI	Done	Sprint 1	False	Feature1
101	Old not relevant	CR	Done	Sprint 2	False	Feature2
102	Old and wrong type	Bug	Done	Sprint 3	False	Feature1
103	Old but relevant	PBI	Done	Sprint 4	False	Feature1
104	Old not relevant	CR	Done	Sprint 5	False	Feature3
105	Current not relevant	PBI	Done	Sprint 6	True	Feature3
106	Current not relevant	PBI	InDev	Sprint BAU	True	Feature2
107	Current and relevant	CR	InDev	Sprint 8	True	Feature1
108	Current but wrong type	Bug	InDev	Sprint 8	True	Feature1

InCurrSprint is a calculated column that checking the Sprint Start and End Dates and comparing them to TODAY() :

InCurrSprint = IF(WorkItems[Iteration Start Date] <= TODAY() && WorkItems[Iteration End Date] > TODAY(),TRUE(), FALSE())

WorkItems[Iteration Start Date] and WorkItems[Iteration End Date] are other columns in the main table not shown above for simplicity.

At any point in time there will be one or more (usually 4-5) sprints in progress; 2-3 of them will be project sprints and 2-3 will be BAU sprints. The project sprints last 2 weeks and are followed new sprints for the remaining project work whereas the BAU sprints never complete.

I want to create a stacked bar chart that shows the count of all PBI and CR work items where the Feature matches a work item in one of the current project sprints. "Current project sprints" basically means any sprint that isn't one of the BAU sprints (whose names are static).

Hence for the example data above I would have a chart like below:

Hence the rows 100, 103 & 107 (Feature 1), 104, 105 (Feature 3)

TIA.

[mbowler]

Thanks for this. Of course my actual data was slightly more complicated than the example I gave (like there being more than one BAU Sprint) but I managed to adapt your measures to suit. Here's one of my final measures:

Count Done = CALCULATE(COUNTROWS(WorkItems),ALLEXCEPT(WorkItems,WorkItems[FeatureName]),WorkItems[Work Item Type] in {"Change Request","Product Backlog Item"},NOT WorkItems[Iteration Path] IN VALUES('BAU Iterations'[BAU Iterations]), WorkItems[State]<>"Removed", WorkItems[DoneInDevOrAnalysed]="Done")

[lbendlin · Accepted solution]

see attached