RANK

RANK is a DAX function that returns the rank of a value within a specified partition based on a specified order.

Syntax

RANK(
   Ties,
   Relation,
   OrderBy,
   Blanks,
   PartitionBy,
   MatchBy,
   Reset
)

Argument	Properties	Description
Ties	Optional	Function behavior in the event of ties. Skip – ranks that correspond to elements in ties will be skipped; Dense – all elements in a tie are counted as one.
Relation	Optional	A table expression where the RANK is computed. If omitted, OrderBy must be explicitly specified.
OrderBy	Optional	Columns that define how each partition is sorted. If omitted, Relation must be explicitly specified.
Blanks	Optional	Defines how to handle BLANK OrderBy values. Valid values include: DEFAULT, FIRST, LAST.
PartitionBy	Optional	Columns that define how Relation is partitioned.
MatchBy	Optional	Columns that define how the current row is identified.
Reset	Optional	Specifies how the calculation restarts. Valid values are: None, LowestParent, HighestParent, or an integer.

Return Values

The rank number for the current context.

Remarks

• Each orderBy, partitionBy, and matchBy column must have a corresponding outer value to help define the current row on which to operate, with the following behavior:

  • If there is exactly one corresponding outer column, its value is used.
  • If there is no corresponding outer column, then:
    • RANK will first determine all orderBy, partitionBy, and matchBy columns that have no corresponding outer column.
    • For every combination of existing values for these columns in RANK parent context, RANK is evaluated and a row is returned.
    • RANK’s final output is a rank number.

• If matchBy is present, then RANK will try to use columns in matchBy and partitionBy to idenfity the current row.

• If the columns specified within orderBy and partitionBy cannot uniquely identify every row in relation, then two or more rows may have the same ranking and the ranking will be determined by the ties parameter.

• RANK returns a blank value for total rows. It’s recommended that you test your expression thoroughly.

• RANK does not compare to RANKX as SUM compares to SUMX.

• reset can be used in visual calculations only, and cannot be used in combination with orderBy or partitionBy. If reset is present, axis can be specified but relation cannot.

The following DAX query:

EVALUATE
ADDCOLUMNS(
    'DimGeography',
    "Rank",
    RANK(
    DENSE,
    'DimGeography',
    ORDERBY(
    'DimGeography'[StateProvinceName], desc,
    'DimGeography'[City], asc),
        LAST,
    PARTITIONBY(
    'DimGeography'[EnglishCountryRegionName])))
ORDER BY [EnglishCountryRegionName] asc, [StateProvinceName] desc, [City] asc

Returns a table that ranks each geography with the same EnglishCountryRegionName, by their StateProvinceName and City. Blank orderBy column values are sorted on the end.

The following visual calculation DAX queries:

SalesRankWithinYear = RANK(DENSE, ORDERBY([SalesAmount], DESC), PARTITIONBY([CalendarYear]))

SalesRankAllHistory = RANK(DENSE, ORDERBY([SalesAmount], DESC))

Create two columns that rank each month by the total sales, both within each year, and the entire history.

The screenshot below shows the visual matrix and the first visual calculation expression:

Related functions

Other functions related to RANK are:

• MOVINGAVERAGE
• RUNNINGSUM

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/rank-function-dax