All eazyBI for Jira eazyBI for Confluence Private eazyBI

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_ExpressionMDX expression that returns a member or an aggregated calculated member.
Level_Expression

MDX expression specifying a member level for the set.

Returns

Set_ExpressionMDX expression that returns a 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 the 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 the example report Multiple sprint overview in our demo account. The calculated measure Sprints completion date uses the formula above.

See also