Measure count Subtask in 3 status except for 1

julieta_Tumburus · October 27, 2023, 2:54am

I need to create a measure that count number of subtask that i have in all status excep for one.
how can i create it ?

zane.baranovska · November 13, 2023, 9:19am

There are several ways you can get the results; the method depends on your report layout. Anyhow, the principle remains the same.

First, group the statuses you are interested in.
In the “Status” dimension, create a new calculated member to group all statuses except for the specified Status. The expression to group all statuses except for “Backlog” might look like this:
```
Aggregate( 
  Except( 
    [Status].[Status].Members, 
    --list of exceptions
    {[Status].[Backlog]}
  ) 
)
```
You can list several statuses as exceptions if needed. See the documentation for more details: Calculated members in other dimensions.
Now you can set the “Status” dimension on pages and select the new calcauted member to filter issues by all statuses except for the specified.
Add the dimension “Issue Type” to report pages to filter only sub-task issues for the report.
If the filters by “Status” and “Issue Type” is not an option for your report (for example, you run the report for other issue types as well), the alternative is to create a new calcauted measure to get the count of sub-task issues in specific statuses.
In Measures, define a new calculated measure. Use a tuple expression of measure “Issue created”, issue type Sub-task and the new calcauted member from Status dimension (from step 1):
```
(
  [Measures].[Issues created],
  [Issue Type].[Sub-task],
  [Status].[Backlog]
)
```
See the documentation for more details: Calculated measures

Here is a Community post on a similar use case: Count Issues In Progress.