Open issues for a suset of components

roberts.cacus · May 31, 2021, 12:18pm

Aggregates in multi-value fields tend to do that. The workaround is using distinct count measures. In the case of “Open issues”, which is a predefined calculated measure, it is based on the number of issues created and resolved. Create a new calculated measure which formula originates from “Open issues” and replace the measures “Issues created”, “Issues resolved”, and “Issues due” with the distinct count measures “Issues created count”, etc. See the formula below:

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
  Cache(
    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
      Cache([Measures].[Issues created count]
          - [Measures].[Issues resolved count])
    ))
    + [Measures].[Issues created count]
    - [Measures].[Issues resolved count]
  )
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due count])
ELSE
  -- optimized formula for drill through Issue
  NonZero(IIF(
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Resolved at'),
        [Time].CurrentHierarchyMember),
    ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues created count]),
    0
  ))
END

See the differences between the original and the distinct measure when evaluated over an aggregated calculated member in the picture below:

Please look at the eazyBI documentation page for more information on defining calculated measures -https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members.

Best,
Roberts // support@eazybi.com