Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
shubhamsaha29
Frequent Visitor

DAX Function for grouping/counting based on filters

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

I have this kind of table with duplicates:

 

shubhamsaha29_0-1683526116728.png

 

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:

 

shubhamsaha29_1-1683526162979.png

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

4 REPLIES 4
FreemanZ
Super User
Super User

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:

FreemanZ_1-1683554135900.png

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

shubhamsaha29_0-1683555135169.png


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
Memorable Member
Memorable Member

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?

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.