Descendants
Returns a 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 a 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 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.
Example 2
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.
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.