I’m totally new to the topic on EazyBI. I would like to setup a report/chart where I could show on X-time-axis (weekly) the amount of issue (type Story) which do not have Story Points.
The following should have affect on the count:
Adding new Story w/o SP --> +1 to the count in the given week
Closing a story w/o SP --> -1 to the count in the given week
Estimating (setting SP) a non closed story --> -1 to the count in the given week
Removing estimation (removing SP) from a non closed story --> +1 to the count in the given week
Can anyone advise on how to achieve this? I was trying to find some guidance in this forum, but w/o luck. ;(
eazyBI has several measures to count story points history over time. However, we do not have a default measure to count issues with no story points over time.
We have default measure (hidden) to count the current amount of issues with story points Issues with Story Points created and you can use subtraction to get a count without Story Points subtracting this measure from all Issues created.
However, you are asking for a historical measure here. You would like to use a calculation on issue level:
Sum(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- filter by open issues in period
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember) AND
DateAfterPeriodEnd(
[Issue].CurrentMember.get('Resolved at'),
[Time].CurrentHierarchyMember) AND
-- filter by issue type
[Measures].[Issue type] = "Story"
),
CASE WHEN
-- filter for issues with no story points in period
CoalesceEmpty([Measures].[Story Points history],0) = 0
THEN
-- count of issues based on all filter criteria
NonZero(([Measures].[Issues created count],
[Time].CurrentHierarchy.DefaultMember))
END
)
Sorry for a delay in response here… did not have time till recently to follow this topic up.
First of all thank you for your guidance. It helped me understand the physics of EazyBI a bit better.
Also I think that the Resolved at period condition does not take into account the issues which are not yet resolved.
I modified the query a bit and at the moment I’m working with this calculation:
Count(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- filter by issues created before period and and not closed before period end
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember) AND
( IsEmpty([Issue].CurrentMember.get('Resolved at')) OR
DateAfterPeriodEnd(
[Issue].CurrentMember.get('Resolved at'),
[Time].CurrentHierarchyMember)
) AND
-- only issues of type Items with SP (it's an aggregation of several issue types)
([Measures].[Issues created],
[Issue Type].[Items with SP],
[Time].CurrentHierarchy.DefaultMember) > 0 AND
-- only the ones which do not have story points
IsEmpty([Measures].[Story Points history])
)
)
The issue which I’m facing right now is the performance of this calculation. It times out most of the times. Could you advise how I could make it more efficient?
I can agree. The calculation is quite tough. Therefore I used a bit different approach in my formula I suggested for you. Sorry, I missed the part of unresolved issues.
I will walk you through how I made my formula for maximal performance optimization. The main idea is to split filters, using filtering by issue properties and only after that apply any measure to already filtered issue set.
I used SUM function instead of COUNT for splitting filters into two parts.
I used only issue properties as criteria to Filter initial issue set. Filter(All issue set, properties as filter criteria only)
Then I used SUM over this filtered issue set:
SUM (filtered issue set, numeric expression)
where numeric expression uses measures Story Points history and Issues created as filters within CASE <> WHEN <> THEN <> END
Measure Story Points history will check if there is no value. Measure Issues created is the main filter for this formula. Measure Issues created works as a counter (numeric expression) for SUM function as well.
You are using a calculated member in Issue type dimension Items with SP in a tuple with measure Issues created. It aggregates several issue types and will multiply any calculation by a number of aggregates issue types. If you are facing performance issues, avoid calculated members if you can. You can use filter by issue property [Meausre].[Issue type] and use MATCHES there to filter by several issue types there.
If the formula still does not work in the account. You can consider minimizing the issue amount per account.
Some measures in eazyBI are not available for the selection to reports, including the measure Issues with story points created. They are hidden measures available for usage in formulas only. You can use them in any formula. And then add this user-defined measure in the report. We are planning to update the MDX autocomplete to present them there with one of the next upcoming versions.