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].[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.
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
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.
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.
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:
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?
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
)
)
)
Firstly about the dimensions and hierarchy - I also have both, eazyBI Portfolio integration creates the Issue hierarchy as well as dimension for each level issues. The dimensions can be used to filter, e.g. only issues with particular Initiative while having the Issue dimension with Portfolio hierarchy on rows.
If this is so and you have the Issue dimension Portfolio hierarchy on rows, your calculation should use this hierarchy. Please try the following
@lpattontr, I am sorry to hear that! This could be due to any items in the report context. Could you please send over the report definition as well as screenshot to support@eazybi.com to investigate why you get this error.
I have to find another way in the near term. I’ll come back to this when I have a chance. Last attempt gave syntax error.
> ERR: mondrian.olap.fun.MondrianEvaluationException: Exception while executing function NonZero: mondrian.olap.fun.MondrianEvaluationException: Expected value of type LEVEL; got value '[Portfolio Story]' (DIMENSION) at mondrian.olap.fun.FunUtil.newEvalException(FunUtil.java:83) at mondrian.rolap.RolapEvaluator.newEvalException(RolapEvaluator.java:1138) at mondrian.calc.impl.GenericCalc.evaluateLevel(GenericCalc.java:162) at mondrian.olap.fun.DescendantsFunDef$3.evaluateList(DescendantsFunDef.java:178) at mondrian.olap.fun.FilterFunDef$MutableIterCalc.makeIterable(FilterFunDef.java:153) at mondrian.olap.fun.FilterFunDef$BaseIterCalc.evaluateIterable(FilterFunDef.java:124) at mondrian.calc.impl.AbstractIterCalc.evaluate(AbstractIterCalc.java:50) at mondrian.olap.ExpCacheDescriptor.evaluate(ExpCacheDescriptor.java:100) at mondrian.rolap.RolapEvaluator.getCachedResult(RolapEvaluator.java:1090) at mondrian.olap.fun.CacheFunDef$1.evaluate(CacheFunDef.java:63) at mondrian.calc.impl.GenericIterCalc.evaluateIterable(GenericIterCalc.java:72) at mondrian.olap.fun.AbstractAggregateFunDef.evaluateCurrentIterable(AbstractAggregateFunDef.java:101) at mondrian.olap.fun.CountFunDef$1.evaluateInteger(CountFunDef.java:59) at mondrian.calc.impl.AbstractExpCompiler$4.evaluateDouble(AbstractExpCompiler.java:371) at mondrian.calc.impl.AbstractDoubleCalc.evaluate(AbstractDoubleCalc.java:45) at mondrian.olap.fun.UdfResolver$CalcExp.evaluateScalar(UdfResolver.java:314) at com.eazybi.mondrian.udf.NonZeroUdf.execute(NonZeroUdf.java:9) at mondrian.olap.fun.UdfResolver$ScalarCalcImpl.evaluate(UdfResolver.java:198) at mondrian.rolap.RolapEvaluator.evaluateCurrent(RolapEvaluator.java:823) at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1153) at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1304) at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1304) at mondrian.rolap.RolapResult.executeBody(RolapResult.java:1016) at mondrian.rolap.RolapResult.<init>(RolapResult.java:504) at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:662) at mondrian.rolap.RolapConnection.access$000(RolapConnection.java:52) at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:613) at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:611) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)
I imagiene this error comes from when you use the formula you provided earlier (or similar):
This is incorrect syntax of Descendants(…) as the function requires the same dimension Parent and how far below children should be returned, but you are using two different dimensions in this function.
I am sorry about the problems this report building is causing to you! I would really need to see the report definition to be able to further assist you.
I have a query related to this topic. I have created two measures:
One to identify the number of issues with no committed story points at the start of the sprint (this one I think works fine).
NonZero(
([Measures].[Issues created]) -
([Measures].[Issues with story points created])
)
Another one to identify which of those issues are finally completed at the end of the sprint (this one works wrong)
NonZero((([Measures].[Issues created]) - ([Measures].[Issues with story points created]) - ([Measures].
([Measures].[Issues with story points created])))),
[Transition Status.Category].[Done].
But in both I am getting exactly the same result, ¿how could I do to identify which of those issues are finally completed at the end of the sprint?
