DescendantsSet

EAZYBI Returns the set of descendants of a member or aggregate calculated member (set argument of Aggregate function) on a specified level.

Syntax

DescendantsSet( Member_Expression , Level_Expression )

Arguments

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

Returns

Set_Expression	MDX expression that returns set of particular level members

Examples

Function DescendantsSet() is suggested when you would like to access details for specific level members, for example, sprints, days, versions, etc. when the same dimension could be used in reports.

Example 1

The following example would pull in all workdays until yesterday for any report selection and count the average of resolved issues per day.

-- count average resolved issues for any weekday for a selected time period
Avg(Filter(
  -- access all days for a selected time period
  DescendantsSet([Time].CurrentHierarchyMember,[Time].CurrentHierarchy.Levels("Day")),
  -- exclude weekend
  NOT [Time].CurrentHierarchyMember.Get('Week day name') MATCHES "Saturday|Sunday"
  AND
  -- count till yesterday only to include full completed days
  DateCompare([Time].CurrentHierarchyMember.StartDate, "Yesterday") <=0
  ),
  -- count resolved issues or 0 if there are no resolved issues in a weekday
  CoalesceEmpty([Measures].[Issues resolved] ,0)
)

See example report Kanban project overview in our demo account. The calculated measure Average throughput uses the formula above to average of daily resolved issues.

Example 2

The following example would access selected sprints to show a completion date. The completion date is a sprint property. It shows any value for individual sprints only. If the report uses a selection of several sprints the formula is needed to access selected sprints individually and pull the latest completion date:

TimestampToDate(MAX( 
    Filter(
      -- get all sprints in any selection
      DescendantsSet([Sprint].CurrentHierarchyMember, [Sprint].CurrentHierarchyMember.Levels("Sprint")),
        -- filter out sprints with complete date only
        NOT isempty([Sprint].CurrentHierarchyMember.get('Complete date'))
      ),
      CASE WHEN  
      ([Measures].[Transitions to],
       [Time].CurrentHierarchy.DefaultMember) > 0
      THEN
      -- Sprint start date to timestamp for Max function
      DateToTimestamp([Sprint].CurrentHierarchyMember.get('Complete date'))
      END
))

See example report Multiple sprint overview in our demo account. The calculated measure Sprints completion date uses the formula above.