Forum Discussion

shubhamsaha29's avatar
shubhamsaha29
Frequent Visitor
3 years ago

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:

 

 

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

DAX

4 Replies

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    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's avatar
      shubhamsaha29
      Frequent Visitor
      3 years ago

      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.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      shubhamsaha29
      Frequent Visitor
      3 years ago

      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.