Combine Calculate measure and Calculate member in report columns

Questions & Answers
, ,
1

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:

image
image874Ă—467 17.1 KB

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

2

Could somebody help me with my problem?

3

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

4

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?

5

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

6

Hi, @michailgoruynov

Here is the sample JavaScript code based on your examples. In the code substitute custom fields:
customfield_MMMMM with your custom fields “MTU Stream” ID in Jira.
customfield_EEEEE with your “Epic Link” custom field ID in Jira.

[jira.customfield_issuesgroup]
name = "Group"
data_type = "string"
dimension = true
javascript_code = '''

//Set Group to Mainenance
if (issue.fields.issuetype.id === "10000" && issue.fields.customfield_MMMMM&& issue.fields.customfield_MMMMM.value === "Maintenance") {
    issue.fields.customfield_issuesgroup = "Maintenance"
} else if (issue.fields.issuetype.id === "15608" && issue.fields.customfield_EEEEE) {
    issue.fields.customfield_issuesgroup = "Maintenance"
}
// Set Group to Incident
else if (issue.fields.issuetype.id === "13901") {
    issue.fields.customfield_issuesgroup = "Incident"
}
// Set Group to Bussiness
else if (issue.fields.issuetype.id === "10000" && issue.fields.customfield_MMMMM && issue.fields.customfield_MMMMM.value === "Business") {
    issue.fields.customfield_issuesgroup = "Business"
}

'''

After adding this to Advanced settings, go to Import options and select this newly created custom field “Group” to be imported as dimension.

Best regards,
Ilze Mezite, support@eazybi.com

7

@ilze.mezite Thanks a lot!

1 Like