I am trying to measure/assess/report on the health of the backlog. One desired metric is the count of issues without story points.
I don’t see this readily available, and I haven’t been able to create a calculated member to do it (the equivalent of the JQL “Story Points” is EMPTY). I also don’t seem to be able to find issues that have estimates (which would let me do some subtraction). All of my attempts seem to fail on a syntax error (anything involving the word “filter”) or return all issues or return some subset of issues that doesn’t make sense to me.
[Measures].[Open issues] and [Measures].[Story points created] seem to be doing what I would expect.
To get a count of issues without story points, you can build a calculation as a count of all Issues created minus issues with story points (for it you may use a hidden measure Issues with Story Points created):
[Measures].[Issues created] - [Measures].[Issues with Story Points created]
Similarly, you can get a count of resolved issues without story points, this time using measures Issues resolved, and hidden measure Issues with Story Points resolved:
[Measures].[Issues resolved] - [Measures].[Issues with Story Points resolved]
Based on those calculations and using the formula of Open issues as a template, you may build “Open issues without Story Points” like this
CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
Cache(
([Measures].[Issues created] - [Measures].[Issues with Story Points created])
- ([Measures].[Issues resolved] - [Measures].[Issues with Story Points resolved]) )
))
+ ([Measures].[Issues created] - [Measures].[Issues with Story Points created])
- ([Measures].[Issues resolved]- [Measures].[Issues with Story Points resolved])
)
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN
([Measures].[Issues created] - [Measures].[Issues with Story Points created])
- ([Measures].[Issues resolved] - [Measures].[Issues with Story Points resolved])
ELSE
-- optimized formula for drill through Issue
NonZero(IIF(
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get('Resolved at'),
[Time].CurrentHierarchyMember) AND
IsEmpty([Issue].CurrentHierarchyMember.get('Story Points')),
([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]),
0
))
END
eazyBI generates a lot of measures when importing a custom field (which is also Story Points). The documentation describes principles of what measures get created for custom fields, but no the list as users could chose to import or not to import custom field.
I am with you on this one; it would improve eazyBI usability if you and other users could find all hidden measures in the documentation or eazyBI measure library. We have some ideas on how to improve this. Meanwhile, you may check principles what measures are created for custom fields: https://docs.eazybi.com/eazybijira/data-import/custom-fields
With version 6.2. we improved MDX autocomplete with a better search and representation of any measure including hidden measures. You can type in any custom field name, for example, Story Points, and autocomplete will show any measure, including hidden ones with the name story points.
This great! I know this is an old post, but can anyone tell me how to exclude epics from the calculations? I know I can remove the rows, but I want a total of issues types without epics.
You can use the dimension “Issue Type” on report pages and specify which issues types should be considered for the report.
Another option is to specify it in the calculation and subtract a tuple of issues with type Epic. The expression might look like this:
--all created issues except for epics
(
([Measures].[Issues created],[Issue Type].DefaultMember) -
([Measures].[Issues created],[Issue Type].[Epic])
) -
--minus all issues with SP except for Epics
(
[Measures].[Issues with Story Points created],[Issue Type].DefaultMember) -
([Measures].[Issues with Story Points created],[Issue Type].[Epic])
)
Thank you, Zane!
I’m not as knowledgeable as I’d like to be, so I wonder if you could show me how the new code would look, in the code you provided previously (pasted below).
CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
Cache(
([Measures].[Issues created] - [Measures].[Issues with Story Points created])
- ([Measures].[Issues resolved] - [Measures].[Issues with Story Points resolved]) )
))
+ ([Measures].[Issues created] - [Measures].[Issues with Story Points created])
- ([Measures].[Issues resolved]- [Measures].[Issues with Story Points resolved])
)
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN
([Measures].[Issues created] - [Measures].[Issues with Story Points created])
- ([Measures].[Issues resolved] - [Measures].[Issues with Story Points resolved])
ELSE
-- optimized formula for drill through Issue
NonZero(IIF(
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get('Resolved at'),
[Time].CurrentHierarchyMember) AND
IsEmpty([Issue].CurrentHierarchyMember.get('Story Points')),
([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]),
0
))
END
What I want is where the row of “All Issue Types” show the totals, is to show the total without Epics. Right now, it counts all the issue types, but we don’t add story points to Epics. So ideally, the total in the top row would be 157 and 28, respectively to the columns.
I would recommend keeping it simple and not updating the code for “Open issues without Story Points” Consider building a new calculated measure and referring to “Open issues without Story Points” using a tuple construction:
--all open issues without SP
([Measures].[Open issues without Story Points],[Issue Type].DefaultMember) -
--except for Open epics without Story point
([Measures].[Open issues without Story Points],[Issue Type].[Epic])
To learn more on tuple constructions, you might check out the documentation and training video:
How can I edit the “Open Issues without Story Points” formula to include 0 story points?
Basically, I want to showcase anything that is equal to “0 OR Less than 0”
@Darshan, you might want to import the Stroy Points as dimension to group issues by their story point values and see how many issues fall in each value group, including “0” and “(none)”. Then you can use this new dimension “Stroy Points” with measures Issus created, Issue resolved, Open issues, and other standard measures representing issue count.
Please see those two community posts for more details on how to do this:
Consider building a new calculated measure and referring to “Open issues without Story Points” using a tuple construction: