Hi @Vladimir_L ,
Welcome to the eazyBI community!
Identification of issues that have been in a specific status at a specific moment requires identification and inspection of each individual issue.
The basic condition translates into the following valuable options:
1)issue got into that status earlier and remained in that status throughout the period
2)issue got into that status during the period and remained in that status throughout the period
3)issue got into that status during the period and exited the status
The predefined measure “Issues history” would cover the options 1) and 2), the measure “Transitions from” would cover the option 3)
That measure retrieves from the database and sums transitions of issues over the previous periods.
The basic expression might be as follows.
Count(
Filter(
--set of issues
DescendantsSet(
[Issue].CurrentMember,
[Issue].[Issue]),
--filter condition
[Measures].[Issues history]>0
OR
([Measures].[Transitions from],
[Transition Status].[In Progress])>0
)
)
However, that expression is a serious performance killer as it potentially looks for historical transitions of all issues , which involves all transitions of all issues in the data cube for each displayed cell.
That might succeed on the test dataset but will likely fail on the production instances.
Therefore, we would like to optimize it as much as possible.
The historical transition measures are slower as they retrieve data from the largest data tables. You might look for the possibility of pre-filtering the issues based on some “absolute” measures to reduce the dataset that calls for historical measures.
Since Count() only allows one filtering level, you might switch to Sum() and use multi-layered filtering.
The updated expression might then be as follows.
Sum(
Filter(
--set of issues
DescendantsSet(
[Issue].CurrentMember,
[Issue].[Issue]),
--pre-filter condition based on absolute dimension context
([Measures].[Issues created],
--resetting dynamic dimension context with .DefaultMember
[Assignee].DefaultMember,
[Project].DefaultMember)>0
),
--numeric value for sum - executed on reduced dataset
--filter condition
CASE WHEN
[Measures].[Issues history]>0
OR
([Measures].[Transitions from],
[Transition Status].[In Progress])>0
THEN
1
END
)
When we look from a different perspective - if the issue has entered or exited that status during the period - it already counts.
So you do not need to look for transitions in previous periods if there are entering or exiting transitions in the current period.
Therefore, you might update the expression as follows.
Sum(
Filter(
--set of issues
DescendantsSet(
[Issue].CurrentMember,
[Issue].[Issue]),
--pre-filter condition based on absolute dimension context
([Measures].[Issues created],
--resetting dynamic dimension context with .DefaultMember
[Assignee].DefaultMember,
[Project].DefaultMember)>0
),
--numeric value for sum - executed on reduced dataset
--filter condition
CASE WHEN
--there were transitions on current period
(
([Measures].[Transitions from],
[Transition Status].[In Progress])
+
([Measures].[Transitions to],
[Transition Status].[In Progress])
)>0
THEN
1
ELSE
CASE WHEN
[Measures].[Issues history]>0
THEN
1
END
END
)
It is possible to further improve the performance depending on the dataset and reporting requirements. You might watch the presentation by Roberts on eazyBI community days 2024 about iteration through issues.
You might find the recordings from eazyBI community days here - eazyBI Community Day 2024: Recordings and Presentations.
If all optimization steps still cannot bring the report to work - there is yet another option - JavaScript-calculated customfield measure.
Still, that option has some limitations:
1) it will only consider the absolute context - current assignee, current priority, current issue type, etc.
2) it can only hold a limited number of records per issue - issues in progress for more than 270 days might run into trouble
Please share the full report context to see the options for optimization or tell me if you are looking towards the JavaScript calculated option.
Regards,
Oskars / support@eazyBI.com