DAX Function for grouping/counting based on filters

[shubhamsaha29]

Hello all,
I need some help figuring out dax for this scenario

I have this kind of table with duplicates:

I want to display count of incomplete workItems for specific sprints.

It should show the count of WorkItems whose state is not closed in specific sprint(say S01) should count as 1 and if closed in S02 then the count should be 0 for that sprint.

Ex:Visual should look like this:

Since WorkItem 111 and 112 is not complete(State not equals Closed) in S01, so counting them as 2 and as in S02 both are in closed state, so not counting them.

DAX which I wrote is not working:

Incomplete =
CALCULATE(
DISTINCTCOUNT(
'UserStories'[Work Item Id]),
FILTER(
'UserStories',
'UserStories'[State]<>"Closed"
))

[FreemanZ]

hi @shubhamsaha29 

try to plot a visual with the sprint column and a measure like:

Incomplete = 
CALCULATE(
    DISTINCTCOUNT(data[WorkItemID]),
    FILTER(
        data,
        NOT "Closed" IN VALUES(data[State])
    )
)+0

it worked like:

[shubhamsaha29]

I tried this but it's not working as expected.

Still getting a value which was closed in S01(Sprint 01 date range - 1/1/2023 to 15/1/2023).

Ex:
WorkItem 113 was closed in S01.
It has was put to Dev on 1/3/2023 and also got Closed on 1/3/2023.

So, Ideally WorkItem 113 should not be counted in S01 in the measure, as it was closed in S01.

[eliasayyy]

hello it wasnt very clear how did you get the result from the sample cahrt you gave us? like how did you get s01 is 2 and s02 is 0 can you please elaborate more?

[shubhamsaha29]

It's based of our Jira board.
Each row is a snapshot our workitems with their fields, like which sprint the Workitem is assigned to, what is it's current state, whom is it assigned to and many more fields which are captured from the Jira tracking page.