All eazyBI for Jira eazyBI for Confluence Private eazyBI

MDX formulas for issue links dimensions

Issue linking in Jira is a powerful feature that helps track relationships between issues. In eazyBI, you can import issue links by configuring advanced settings for custom fields. Once imported as a dimension, you may need additional formulas to analyze data related to linked issues.

Below are calculated measure examples to help you get started. Simply copy them into your eazyBI account and replace "Bugs" with the actual name of your issue link dimension.

Keep in mind that to retrieve the information from the linked issues, those linked issues should be imported into the eazyBI account; the linked issue should be present in the "Issue" dimension, too.

On this page

Bugs created date

You can access linked issue properties in the same way as issue properties; to access the issue property, use function get(). The key difference is that instead of using [Issue], you need to reference the linked issue dimension name in the formula. 

[Bugs].CurrentHierarchyMember.get('Created at')

Bugs Status and Bugs Assignee

Some linked issue properties return another dimension's ID instead of a readable value. In such cases, you need to retrieve the corresponding member name using the GetMemberNameByKey() function. For example, to get the Bugs Status name:

[Status].[Status].getMemberNameByKey(
  [Bugs].CurrentHierarchyMember.get('Status ID')
)

or to get the Bugs Assignee name:

[Assignee].[User].GetMembernameByKey(
  [Bugs].CurrentMember.Get('Assignee name')
)

When in doubt about how to correctly address the property, find the issue property in the Measures and copy the expression eazyBI has generated. Then, use the copied expression and replace [Issue] with the linked issue dimension name.

Linked Bugs count

The formula below counts bugs linked to issues, either at the individual issue level (using issue properties) or at an aggregated level (using the Bugs dimension).

CASE WHEN --individual issues on report rows
  [Issue].CurrentMember.Level.Name = "Issue"
  AND
  [Bugs].CurrentMember is [Bugs].DefaultMember
THEN
  -- retrieve Bugs information from Issue property Bugs
  Nonzero(Count(
    [Issue].CurrentHierarchy.GetLinkedMembers('Bugs')
  ))
ELSE
 -- total calculation for any issue, data on Bugs level
  NonZero(Sum(
    Descendants([Bugs].CurrentMember, [Bugs].[Bugs]),
    -- counts how many times Bugs are reference with issues, this works as a filter as well
    [Measures].[Issues created]
 ))
END

Linked Bugs Done

This formula counts resolved or completed bugs, either linked to specific issues (when viewed at the Issue level) or as a total across all issues (when viewed at higher levels or with a Bugs selection).

CASE WHEN --individual issues on report rows
  [Issue].CurrentMember.Level.Name = "Issue" 
  AND
  [Bugs].CurrentMember is [Bugs].DefaultMember
THEN
  -- retrieve Bugs information from Issue property Bugs
  Nonzero(Count(
    Filter(
      [Issue].CurrentHierarchy.GetLinkedMembers('Bugs'),
      [Measures].[Issue status] = "Done")
  ))
ELSE
 -- total calculation for any issue, data on Bugs level
  NonZero(Sum(
    Filter(
      DescendantsSet([Bugs].CurrentMember, [Bugs].[Bugs]),
      [Status].[Status].GetMemberNameByKey(
        [Bugs].CurrentHierarchyMember.Get('Status ID')
      ) = "Done"),
      -- counts how many times Bugs are reference with issues, this works as a filter as well
    [Measures].[Issues created]
 ))
END

Hours spent on Linked Bugs

This formula calculates hours spent either on bugs linked to an issue (when viewed at the Issue level) or directly on the bugs themselves (when viewed at the Bugs level). It ensures the correct measure context for time periods and users who logged the work.

CASE WHEN
  [Issue].CurrentMember.Level.Name = "Issue"
  AND
  [Bugs].CurrentMember is [Bugs].DefaultMember
THEN
  -- retrieve Bugs information from Issue property Bugs
  CASE WHEN
    NOT isEmpty([Issue].CurrentHierarchyMember.Get('Bugs'))
  THEN
    NonZero(SUM(
    [Issue].[Issue].GetMembersByKeys(
      [Issue].CurrentHierarchyMember.get('Bugs')),
      DefaultContext((
        [Measures].[Hours spent],
        [Issue].CurrentMember,
        [Time].CurrentHierarchyMember,
        [Logged by].CurrentMember))
    ))
  END
ELSE
 -- total calculation for any issue, data on Bugs level
  NonZero(SUM(
    Filter(
      Descendants([Bugs].CurrentMember, [Bugs].[Bugs]),
      -- filter out Bugs with reference to Issue
      ([Measures].[Issues created],
      [Logged by].DefaultMember, 
      [Time].CurrentHierarchy.DefaultMember) > 0),
    -- search for Bug in Issue dimension, use in a tuple with measure, logged by and time, ignore anything else with DefaultContext    
    DefaultContext((
      [Measures].[Hours spent],
      [Issue].[Issue].GetMemberByKey([Bugs].CurrentMember.Key),
      [Logged by].CurrentMember, -- allow selection by Logged by
      [Time].CurrentHierarchyMember, -- allow selection by Time
      [Bugs].DefaultMember
    ))
 ))
END

This formula generates a markdown-formatted list of linked bug issues, each shown on a separate line with a clickable Jira URL and its current status. To ensure correct display, apply Markdown formatting to the measure. Additionally, replace 'https://eazybi-training.atlassian.net/browse/' with your own Jira URL in the formula.

CASE 
  WHEN [Bugs].CurrentMember is [Bugs].DefaultMember
  THEN
    Generate(
      -- get a list of linked issues
      [Issue].[Issue].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Bugs')
      ),
      -- show by key
      "[*" ||
      Cast([Issue].CurrentHierarchyMember.Key as string) ||
      "*](https://eazybi-training.atlassian.net/browse/" ||
      Cast([Issue].CurrentHierarchyMember.Key as string) ||
      ")" ||
      " - " ||
      -- show issue status 
      [Status].[Status].GetMemberNameByKey(
        [Issue].CurrentHierarchyMember.Get("Status ID")
      ),
      Chr(10)
    )
END

Issue bug count per status

This formula generates a text list displaying each status alongside the count of linked bug issues for that status, with each status-count pair appearing on a separate line.

Generate(
  Filter(
    [Status].[Status].Members,
    Count(
      Filter(
        [Issue].CurrentHierarchy.GetLinkedmembers('Bugs'),
        [Measures].[Issue status] = [Status].CurrentMember.name
      )
    ) > 0
  ),
  [Status].CurrentMember.name
  || " - " 
  || Cast(
    Cast(
      Count(
        Filter(
          [Issue].CurrentHierarchy.GetLinkedmembers('Bugs'),
          [Measures].[Issue status] = [Status].CurrentMember.name
        )
      ) as integer
    ) as string
  ),
  Chr(10)
)

Average Bugs creation days after issue resolution

This formula calculates the average number of days between an issue's resolution and the creation of its linked bugs, considering only bugs created after resolution to help identify potential premature closures.

CASE 
  WHEN [Issue].CurrentMember.Level.Name = "Issue"
    AND [Bugs].CurrentMember IS [Bugs].DefaultMember
  THEN
    NonZero(
      AVG(
        [Bugs].[Bugs].GetMembersByKeys(
          [Issue].CurrentHierarchyMember.Get('Bugs')
        ),
        CASE 
          WHEN DateCompare(
            [Issue].CurrentHierarchyMember.GetDate("Resolved at"),
            [Bugs].CurrentHierarchyMember.GetDate("Created at")
          ) < 0
          THEN DateDiffDays(
            [Issue].CurrentHierarchyMember.GetDate("Resolved at"),
            [Bugs].CurrentHierarchyMember.GetDate("Created at")
          )
        END
      )
    )
  WHEN [Issue].CurrentMember.Level.Name = "Issue"
    AND [Bugs].CurrentMember.Level.Name = "Bugs" 
  THEN
    CASE 
      WHEN DateCompare(
        [Issue].CurrentHierarchyMember.GetDate("Resolved at"),
        [Bugs].CurrentHierarchyMember.GetDate("Created at")
      ) < 0
      THEN DateDiffDays(
        [Issue].CurrentHierarchyMember.GetDate("Resolved at"),
        [Bugs].CurrentHierarchyMember.GetDate("Created at")
      )
    END
END

Issues created by Bug Priority

This formula counts the number of linked bugs for issues, considering only those bugs that match the priority selected in the report—ignoring the priority of the parent issue itself.

For a deeper understanding of context changes in reports, watch the training video Advanced MDX: Recipe to Change Report Context.

Sum(
  --go through all linked Bugs
  Filter(
    DescendantsSet([Bugs].CurrentMember,[Bugs].[Bugs]),
    --check if linked Bug Priority matches selected Priority
    DefaultContext((
      [Measures].[Issues created],
      [Issue].[Issue].GetMemberByKey(
        [Bugs].CurrentMember.KEY),
      [Priority].CurrentHierarchy.CurrentMember
    )) > 0
  ),
  --aggregate linked Bugs ignoring the Issue Priority
  ([Measures].[Issues created],
  [Priority].CurrentHierarchy.DefaultMember)
)

Report examples

The reports below from the training account include the formulas mentioned on this page, along with additional examples. Explore these reports to see how the formulas work in practice. You can copy them and replace Bugs with the actual name of your issue link dimension.

See also