Rank (PQL)

Returns the rank of a member in the specified list,

Syntax

Rank( <Member> , <List> , OPTIONAL <Data Point> )

* Click on the function's arguments above for more details on the input values.

Comments
  • The member can be any selected member or a functional representation of a member.
  • List must be a list or set of elements from the SAME hierarchy as the member in the model.
  • The optional data point or tuple describes the values used to determine the ranking. If not supplied, the member is ranked based on the order of elements in the list. If the list is not specifically ordered, then its NATURAL order is used.
  • For details on how to employ and use this function see the semantic calculation overview.
Different Function types
  • This function is like the MDX rank function
  • To use a simple rank mathematical operation on a list of numbers, use the Common library function.

Examples

This example returns the rank of each promotional item in the list of promotions.

The simple rank (second measure in the grid below), using nothing but the promotion item, ranks it in the natural order of the elements of the hierarchy. Note the use of "currentmember" to select the current promotion per cell in the grid.

Rank( [promotions].[Promotion].CurrentMember. {AllMembers([promotions].[Promotion])} )

The numeric rank (third measure in the grid below), using but the promotion item's sales value and ranks it in the order of the other promotional element sales. So Half-Price pedal sale is still ranked "1" - since it has the lowest sales in the list, while "No Discount" is ranked 15th, since it is the second highest sales value in the list.

Rank( [promotions].[Promotion].CurrentMember. {AllMembers([promotions].[Promotion])}, ([measures].[data Sales]) )