Descendants

Returns a set of descendants of a member at a specified level.

Syntax

Descendants(Member_Expression, Level_Expression)

Arguments

Returns

Examples

Issues created and resolved in the same period

For reports based on Jira data, function Descendants() is occasionally used to aggregate issues by some issue property that is not available as a dimension or a measure or to apply complex filtering criteria.

The following example would return the issue count of issues created and resolved in the same period (complex filtering criteria).

NonZero(
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] 
))

See the example report Issues created and resolved in period % in our demo account.  The calculated measure Issues created and resolved in period uses the formula above to count resolved issues that was created in the same period.

Count of completed Sprints

The following example would return a count of completed Sprints. Function Descendants() allows to drill across Sprints and see the list of particular completed sprints based on this measure.

NonZero(
Count(
  Filter(
    Descendants([Sprint].CurrentMember, [Sprint].[Sprint]),
    [Sprint].CurrentMember.GetBoolean("Closed")
    AND
    [Measures].[Issues created]>0
  )
))

See the example report Sprints and Story Points overview for project in our demo account. The calculated measure Completed Sprints uses the formula above.

Issues resolved before their due date

The following formula returns the count of issues resolved that have been resolved before their due date.

NonZero(
  Sum(
    Filter(
      --iterate through issues
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      -- issue has resolution date
      NOT IsEmpty([Issue].CurrentMember.Get('Resolved at'))
      AND
      -- and resolved before due date
      DateCompare(
        [Issue].CurrentMember.Get('Due date'),
        [Issue].CurrentMember.Get('Resolved at')
      ) >= 0
    ),
    [Measures].[Issues resolved]
  )
)

Addressing members and levels

In the Descendants function, you should pass the hierarchy and the level through which you would like to iterate. For example, to iterate through the issues hierarchy issues level, pass the following parameters:

Descendants([Issue].CurrentMember, [Issue].[Issue])

If you wish to iterate through the Epic level members in the Epic hierarchy, you can pass the following parameters:

Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Epic])

To iterate through the Parent level of the Sub-task hierarchy, use the following parameters:

Descendants([Issue.Sub-task].CurrentMember, [Issue.Sub-task].[Parent])

See also

• Function Filter(), which is commonly used together.
• Function DescendantsSet().
• Aggregate function Count().
• Function ChildrenSet() or CascadingChildrenSet() to get members of an aggregate calculated member.