Backlog health: count of issues without story points

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.

Some of the things that I have tried:

  • [Measures].[Open issues], IsEmpty([Measures].[Story Points created])
  • [Issue].CurrentHierarchyMember, ([Issue].CurrentHierarchyMember.get(‘Story Points’) > 0)
  • [Issue].CurrentMember, ([Measures].[Story Points created] is null)

Any help/direction would be greatly appreciated!

Hi @viviane,

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

Best,
Zane / support@eazyBI.com

1 Like

Thank you, Zane. Those hidden measures for Issues with Story Points created and Issues with Story Points resolved are priceless.

Is there a listing of all of the hidden measures?

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

Best,
Zane / support@eazyBI.com

2 Likes

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.

Daina / support@eazybi.com

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.

Hi @camgray,

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

Best,
Zane / support@eazyBI.com

1 Like

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.
image

I would recommend keeping it simple and not updating the code for “Open issues without Story Points” :slight_smile: 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: