Rows of Components with Measure showing each components oldest bug Key

Questions & Answers
1

Hello!
I’m creating Components overview table with data points on each component. I would also like to show oldest bug, latest Epic, highest prio issue, etc. The measure needs to output “KEY” of the issue so that it can be transformed into direct link with markdown.
I have this working, but I’m quite sure there must be a better way to do this.
Screenshot 2024-04-24 at 10.03.42

The main problem of current solution is speed and also I would like to access and sort by any of the issue properties. For example to get the the number of days since last Closed Epic.

Current MDX:

Order(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Issue type] = "Bug"
    AND
    [Measures].[Issue status] <> "Done"
    AND
    [Measures].[Issues created]>0),
  [Issue].CurrentMember.Get('Created at'),
  ASC
).Item(0).Get('KEY')
2

Hi @mucenieks,

The bottleneck of this approach is the necessity of iterating through all issues in the data cube to identify the oldest, highest priority, latest, etc.
While iterating through the issues is still required, it might be optimized by reducing the dataset and rearranging conditional clauses into steps.

If you are only interested in issue current values - you might create custom hierarchies based on Issue Type or Issue Priority within the Issue dimension.
If you have multiple projects within the data cube, issues are distributed among projects, and only a fraction of issues is actually in scope for the report - you might pre-filter the projects and only include issues from these projects in the scope for inspection.

The existing code for finding the oldest bug that has not yet been done is quite good.
I would change the direct reference to the property of the first item in the set .item(0).get(“”) into a different construction to handle the potential error in case of an empty set.
I would also rearrange the conditions so that issues are first filtered by their properties and measure value is only requested for a reduced dataset.

The updated expression might then be as follows.

Generate(
  Head(
    Order(
     Filter(
      DescendantsSet(
        [Issue].CurrentMember,
        [Issue].[Issue]),
--filter conditions on issue properties
        [Measures].[Issue type] = "Bug"
       AND
       NOT [Measures].[Issue status] = "Done"
      ),
--value for ordering
--only check for relevant issues
     CASE WHEN
      [Measures].[Issues created]>0
     THEN
      DateToTimestamp([Issue].CurrentMember.Get('Created at'))
     END,
--order direction
     BASC),
--number of items from the beginning
    1),
--string value to be returned for Generate
   CAST([Issue].CurrentMember.Key as string),
--split by comma - for compatitbility reasons
 ",")

Different optimization techniques could be used, depending on the size of the dataset, report context, and distribution of issues across different categories.
Still, identifying the individual issue requires iteration through the issue dimension, and that takes time.

Regards,
Oskars/ support@eazyBI.com