Count of epics and non epics issues

Can anyone help me with the following: I want to count how many issues (both epics and non epics) we have with the hours spent less than 5 h. I created two measurements for that - first calculates how many issues I have in epics and non epics, second: summarize those two measurements.
1)[Measures].[a]=
CASE [Issue.Epic].CurrentMember.Name WHEN “(no epic)” THEN
Count(Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
Cache([Measures].[Hours spent] > 0 AND [Measures].[Hours spent] <= 5)
))
ELSE
Count(Filter(Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Epic]),
Cache([Measures].[Hours spent] > 0 AND [Measures].[Hours spent] <= 5)
))
END
2)Effort [<= 5h] =
Cache(
Sum(
– set of previous time periods until selected time period
{[Issue.Epic].[GMCS Business Requests].[(no epic)],[Issue.Epic].[GMCS Business Requests]},
– numeric expression
Cache([Measures].[a])
))

It seems to work correct when I use all issues, but when I start to add time split or group of “Logged by”, it runs super slow and I can’t create the report at the end.
Is there any way to optimize my measurements?

Hi,

As I can read from your formulas, you are trying to count the epics having less than five hours from a specific project GMCS Business Requests. The problem with your formulas seems related to inconsistent use of the hierarchies of the issue dimension.

Note that the “no epic” branch in the epic hierarchy aggregates all the stories without the epic link, all the other stories are put in the hierarchy under the epic links. Note also, that once you use the Epic hierarchy for counting hours, the epic level consolidates all the hours from the lower level issues under the epic.

I would suggest trying out one of those formulas, providing the count of issues with less than five hours in two ways.

  1. Count of issues (regardless of the hierarchy and issue type) having logged hours less than five:

    NonZero(Count(
    Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Hours spent]>0 AND
    [Measures].[Hours spent]<5
    )
    ))

  2. Count of the epics having the logged hours less than five, including the issues in the hierarchy under the epic:

    NonZero(Count(
    Filter(Descendants([Issue.Epic].CurrentHierarchyMember,
    [Issue.Epic].[Epic]),
    [Measures].[Hours spent]>0 AND
    [Measures].[Hours spent]<5
    )
    ))

Such measures allow creating a report like this:

The use of this measure with the Logged by dimension should also work, but you need to take into account that now the Hours spent measure is limited to the specific user and shows the count of issues where the user has reported less than 5 hours.

Kindly,
Janis, eazyBI support