Combine Calculate measure and Calculate member in report columns

Hello, eazyBI support team.

I extremly need your advise.
I have 3 Calculate members of Issue dimension. For example, one of them:

Sum(
Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Issue].CurrentHierarchyMember.get('Issue type ID') = 10000 AND
      [Issue].CurrentHierarchyMember.get('MTU Stream') = "Business" 
)
)

and with them I use 3 measures:

  • Issue Created
  • Issue Resolved
    and the third is a calculate measure that counts issues created and resolved in the same period
Sum(
  Filter(
  -- iterate through set of issues
  Descendants([Issue].CurrentMember,[Issue].[Issue]),
  -- apply filter criteria to each issue
  DateInPeriod( 
    [Measures].[Issue created date],
    [Time].CurrentHierarchyMember) AND
  DateInPeriod( 
    [Measures].[Issue resolution date],
    [Time].CurrentHierarchyMember)
  ),
 -- numeric expression - sum of relevant issues
   [Measures].[Issues created]
))

When I try to combine them together I don’t have any data in the third measure. I think it’s because of using Filter in both Calculate measure and Calculate member:

Could somebody help me with this? Thanks in advance for your help.

Could somebody help me with my problem?

Hi @michailgoruynov

Creating calculated members in the Issues dimension is tricky, as they may return unexpected or empty values when used together with measures that iterate through issues (as in your case). The calculation formula for the measure “Issues created and resolved in period” itself seems correct.

Based on your calculated member’s formula, it seems you have a hidden logical “dimension” or, in other words, a new aspect of how to group issues based on combinations of two independent fields (issues type and MTU Stream).
In such case, we suggest creating a calculated JavaScript custom field that, during data import, checks values in issue type and MTU Stream fields and, based on their value combination, assigns values Business, Maintenance, or Incidents in this new field for each issue. More about in documentation: JavaScript calculated custom fields
Advanced settings for such a field would be the following:

[jira.customfield_issuesgroup]
name = "Group"
data_type = "string"
dimension = true
javascript_code = '''
 write your JavaScript code there
'''

Then you would select this field to be imported as a dimension. Finally, you would use it in the report columns instead of Issue calculated members.

Best,
Ilze, support@eazybi.com

Hi @ilze.leite

For Business, Maintenance and Incidents I have different logics. For example, for Maintenance:

Sum(
Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      (
        [Issue].CurrentMember.get('Issue type ID') = 10000 AND
        [Issue].CurrentMember.get('MTU Stream') = "Maintenance"
      ) OR
      (
        [Issue].CurrentMember.get('Issue type ID') = 15608 AND
        NOT IsEmpty([Issue].CurrentMember.get('Epic Link'))
      )
)
)

for Incidents:

Sum(
Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Issue].CurrentHierarchyMember.get('Issue type ID') = 13901
)
)

Should it still be a one group with mixed logics?
Could you help me with Javascript code, based on my 3 examples?

@ilze.leite or @eazyBI-Staff Hi, could you help me with my problem?