topic Re: DAX Function for grouping/counting based on filters in DAX Commands and Tips

DAX Function for grouping/counting based on filters

shubhamsaha29 — Mon, 08 May 2023 06:16:03 GMT

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"
))

Re: DAX Function for grouping/counting based on filters

eliasayyy — Mon, 08 May 2023 06:25:31 GMT

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?

Re: DAX Function for grouping/counting based on filters

shubhamsaha29 — Mon, 08 May 2023 06:34:24 GMT

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.

Re: DAX Function for grouping/counting based on filters

FreemanZ — Mon, 08 May 2023 13:55:44 GMT

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:

Re: DAX Function for grouping/counting based on filters

shubhamsaha29 — Mon, 08 May 2023 14:26:47 GMT

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.