Descendants

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

Syntax

Descendants( Member_Expression , Level_Expression )

Arguments

Member_Expression	MDX expression that returns a member.
Level_Expression	MDX expression specifying a member level for the set.

Returns

Set_Expression	MDX expression that returns set of particular level members

Examples

Example 1

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

Following example would return 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 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.

Example 2

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 example report Sprints and Story Points overview for project in our demo account. The calculated measure Completed Sprints uses the formula above.