Order

Returns an arranged set of members by a specified value, optionally preserving or breaking the hierarchy.

If you want to order the report rows by the measure value, you can explore standard functionality.

Syntax

Order(Set_Expression, Value_Expression)

or

Order(Set_Expression, Value_Expression, Order_Expression)

Arguments

Set_Expression

MDX expression that returns set.

Value_Expression

Value by which this set should be ordered.

Order_Expression

ASC, DESC - orders ascending or descending within members' hierarchy.

BASC, BDESC - orders ascending or descending overriding members' hierarchy.

Examples

The following example would order the resolved sub-tasks by their resolution date in descending order and then return the resolution date of the last resolved sub-task of the issue.

Order(
  -- set of sub-tasks for the issue
  Filter(
    Descendants([Issue.Sub-task].CurrentMember, [Issue.Sub-task].[Sub-task]),
    NOT IsEmpty([Issue.Sub-task].CurrentMember.Get('Resolved at'))
  ), 
  -- value for ordering
  [Issue.Sub-task].CurrentMember.Get('Resolved at') ,
  -- order expression
  DESC
).Item(0).Get('Resolved at')

The next example is eazyBI default calculated member in Sprint dimension All closed sprints. The calculated member pulls in all sprints from any board and orders them by the start date. Symbol BASC is used to ignore boards and order sprints from any board by start date:

Aggregate(
  Order(
    -- set to order
    Filter(
      [Sprint].[Sprint].Members,
      [Measures].[Sprint Closed?] = 'Yes' 
      AND
      NOT IsEmpty([Sprint].CurrentMember.Get('Complete date'))
    ),
    -- value for ordering
    [Sprint].CurrentMember.Get('Start date'), 
    -- order expression
    BASC
  )
)

The sample report Sprint velocity chart in our demo account uses the calculated member All closed sprints in Sprint dimension to show only completed sprints in the report.