Measure to get the number of parent issues only if their subtask is in specific status

Questions & Answers
, ,
1

Hi,
I am struggling with MDX and custom measures.
I am looking to create a custom measure that would return the number of issues (from issue created), but only if those issues have a subtask (of type “deployment”) that is either in status “A” or “B”
I believe in need to use the Filter function somehow but really have no clue.
Can anyone help me?

2

Hello @antoinelu
Welcome to eazyBI community!

When reading your requirement, I would recommend you first start with creating a calculated member “A and B statuses” within the Status dimension to aggregate the required statuses. I have named this calculated member “A and B statuses”, which I will use in the next formula. If you name the calculated member differently, please make sure to also update the second formula.

Aggregate(
  {[Status].[A],
  [Status].[B]}
)

When that is done, you might use the following formula to define a calculated measure in Measures dimension to count the issues, if there is a subtask with type “Deployment” and in status “A” or “B”

NonZero(Count(
  Filter(Descendants([Issue.Sub-task].CurrentHierarchyMember,
    [Issue.Sub-task].[Parent]),
    ([Measures].[Issues created],
     [Issue Type.By type].[Sub-Task].[Deployment],
     [Status].[A and B statuses])>0
    )
))

Please don’t hesitate to contact me if you need any additional information.
Elita from support@eazybi.com

3

Thanks Elita, that seems to work.
Do you have a documentation on MDX for EazyBI that I could follow? I have checked your site but what I did not find was the way Jira data is modelised/translated within EazyBI.
Everytime I need to make a report that involves custom measures, that is quite hard to picture on how to do it, which functions to use etc.
Thanks.

4

Hi @Elita.Kalane,
Infact the measure works but since I then filter the report on “issuetype = Bug”, nothing is displayed in the column related to the measure. I believe this is because the tuple in the measure you’ve shared takes into account [Measures].[Issues created] which comes to be only bugs after I filter the report.
How can I handle this? Should I not use the page to filter out bugs issue type and use a tuple on issues created instead?
Or is there a better way to create the “Deployment” and in status “A” or “B” measure that would work with the page set to display only bugs?

5

Hello @antoinelu

Thanks for following up and giving more details about your report!

Regarding your question about learning and understanding MDX, I would recommend watching these videos here - Training videos on specific topics. They are very useful when learning to write MDX calculations and also debugging the code.

For your calculated measure - please amend the previous code to the one here below (make sure you replace “A and B status” with your aggregated calculated member) and see if you are now able to filter results on the issue type?

NonZero(Count(
  Filter(Descendants([Issue.Sub-task].CurrentHierarchyMember,
    [Issue.Sub-task].[Parent]),
    ([Measures].[Issues created],
     [Issue Type].[Deployment],
     [Status].[A and B status]
     )>0 AND
     [Measures].[Issues created]>0  
)))

Please don’t hesitate to contact me if you need any additional information.
Elita from support@eazybi.com