RANK DAX Function

Returns the rank for the current context within the specified partition sorted by the specified order or on the axis specified.

Syntax

RANK ( [<Ties>] [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] [, <Reset>] )
Parameter Attributes Description
Ties Optional

DENSE or SKIP.
Defines how to handle the ranking when two or more rows are tied. If omitted, SKIP is the default.

Relation Optional

A table expression from which the output row is returned.
If specified, all columns in OrderBy and PartitionBy must come from it.
If omitted, it defaults to ALLSELECTED() of all columns in OrderBy and PartitionBy, and OrderBy must be explicitly specified.

OrderBy Optional

An ORDERBY() clause containing the columns that define how each partition is sorted.
If omitted, Relation must be explicitly specified, and it orders by every column in Relation that is not already specified in PartitionBy.

Blanks Optional

An enumeration that defines how to handle blank values when sorting.
The supported values are:

  • DEFAULT (the default value), where the behavior for numerical values is blank values are ordered between zero and negative values. The behavior for strings is blank values are ordered before all strings, including empty strings.
  • FIRST, blanks are always ordered on the beginning, regardless of ascending or descending sorting order.
  • LAST, blanks are always ordered on the end, regardless of ascending or descending sorting order.

Note, when blanks parameter and blanks in ORDERBY() function on individual expression are both specified, blanks on individual OrderBy expression takes priority for the relevant OrderBy expression, and OrderBy expressions without blanks being specified will honor blanks parameter on parent Window function.

PartitionBy Optional

A PARTITIONBY() clause containing the columns that define how Relation is partitioned.
If omitted, Relation is treated as a single partition.

MatchBy Optional

A MATCHBY() clause containing the columns that define how to match data and identify the current row.

Reset Optional

Specifies how the calculation restarts. Valid values are: None, LowestParent, HighestParent, or an integer.

Return values

Scalar A single integer value.

The rank number for the current context.

» 2 related articles

Examples

DEFINE
    MEASURE Sales[Rounded Sales] =
        MROUND ( [Sales Amount], 400000 )
    MEASURE Sales[Rank] =
        VAR SourceTable =
            ADDCOLUMNS ( ALLSELECTED ( Product[Brand] ), "@Amt", [Rounded Sales] )
        VAR Result =
            RANK ( DENSE, SourceTable, ORDERBY ( [@Amt], DESC, Product[Brand], ASC ) )
        RETURN
            Result

EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Category],
    'Product'[Brand],
    TREATAS ( { "Audio", "Computers" }, 'Product'[Category] ),
    "Sales Amount", [Sales Amount],
    "Rank", [Rank]
)
ORDER BY
    'Product'[Category],
    [Rank] ASC

Category Brand Sales Amount Rank
Audio Contoso 170,194.00 1
Audio Northwind Traders 60,942.07 2
Audio Wide World Importers 153,382.09 3
Computers Proseware 1,905,253.77 1
Computers Adventure Works 1,646,596.85 2
Computers Wide World Importers 1,422,852.03 3
Computers Contoso 1,054,179.83 4
Computers Fabrikam 550,289.94 5
Computers Southridge Video 162,376.31 6

Related articles

Learn more about RANK in the following articles:

Last update: May 23, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

Microsoft documentation: https://learn.microsoft.com/dax/rank-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of RANK? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.