How to Count Issues (History) without Story Points

calculatedmembers

#1

Hi everyone,

i want to create a chart which displays the number of issues without storypoints (isempty([Issue].get (“Story Points”)?) or With 0 Storypoints for any given date (issue history).
I tried this code

Count
(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issues created] >0
AND
(
[Measures].[Story Points history] = 0
OR
IsEmpty([Measures].[Story Points history])
)
)
)

and also this one

count(
Filter(
Descendants(
[Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issue history]>0
and
(
isEmpty([Issue].get(‘Story Points’))
or
[Issue].get(‘Story Points’)=0
))
)

where both actually arent working properly, at least the values are not correct. Any suggestion what is wrong with this code? I also tried some variations with the [Issue History] Measure, but both history-measures in combination are crashing the report.

Regards,

Lars


Descendants filter to only one level
#2

Hi Lars,

You were on the right track with the first formula! The incorrect part there is that you first filtered ‘Issues created’ which on Timeline gives only issues created in that period. So you ended up counting just issues created at the period which do not have story points at that period.

Try the following formula - it first filters that issue is created before the end of the selected period end and then makes sure issue is created in all other report context, ignoring Time.

NonZero(Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
    ([Measures].[Issues created],
      [Time].CurrentHierarchy.DefaultMember) >0
    AND
    ([Measures].[Story Points history] = 0
      OR
      IsEmpty([Measures].[Story Points history])
    )
  )
))

Please let me know if you have further questions regarding this!
Lauma / support@eazybi.com


#3

Thank you very much, worked perfect for me!


#4

I want something very similar but not by Time. Just a straight cumulative count of issues with and without, grouped by parent Epic Link. When I edit the above to remove [Time] I get no data.


#5

Hi @lpattontr!

There is a hidden measure ‘Issues with story points created’, see https://docs.eazybi.com/eazybijira/data-import/custom-fields#CustomFields-Totalsofcustomnumberfields. This can be used to get also Issues without Story points:

NonZero([Measures].[Issues created] - 
[Measures].[Issues with story points created])

Lauma / support@eazybi.com


#6

Hm… that’s still not giving me the total number of stories with points. Maybe b/c I’m using Portfolio Epics, not Epic Link. My formula:
Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Story Points history] >= 0
)
)
This gives me almost the right number (seems to include the parent Epic itself in the count of total issues). Also getting a timeout when I try to run in a secondary calculated, with a ratio.


#7

@lpattontr,

The formula is going through issues in the default hierarchy, not the [Issue.Portfolio].[Story] hierarchy and level. This is why there is the timeout.

Still, I imagine the fastest would be to use the hidden “issues with…” measures. To get only the Stories without Story points, try the following formula that overrides the issue type:

NonZero(
  ([Issue Type].[Story], [Measures].[Issues created]) - 
  ([Issue Type].[Story], [Measures].[Issues with story points created])
)

Here is a screenshot of how this works in Portfolio hierarchy:

If you experience any problems with this measure, please send more details of your report, including screenshots, to our support e-mail.

Lauma / support@eazybi.com


#8

I tried this and it is not giving me the results I expect.

Also I am a bit confused. Our system has a completely separate set of dimensions for Portfolio which is what I have been using. Your screenshot shows “portfolio” items as part of the Issue hierarchy itself. We have that too. What’s the difference?


#9

But at least you gave me a clue not to mix Portfolio dims with Issue dims. Tried this and it’s almost what I want: Still it’s counting the Epic itself as having story points, so it’s off by one.
–annotations.group=Agile
– Has points if story points history measure is greater than 0
Cache(Count(
Filter(
Descendants([Portfolio Epic].CurrentHierarchyMember, [Portfolio Story].[Portfolio Story]),
[Measures].[Story Points history] >= 0
)
)
)