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
| Parameter | Attributes | Description |
|---|---|---|
| Ties | Optional |
DENSE or SKIP. |
| Relation | Optional |
A table expression from which the output row is returned. |
| OrderBy | Optional |
An ORDERBY() clause containing the columns that define how each partition is sorted. |
| Blanks | Optional |
An enumeration that defines how to handle blank values when sorting.
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. |
| 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
The rank number for the current context.
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:
-
Introducing the RANK window function in DAX
RANK is a new DAX function to rank items based on multiple columns. This article introduces the RANK function and its differences with RANKX. » Read more
-
Preparing a data model for Sankey Charts in Power BI
This article describes how to correctly shape a data model and prepare data to use a Sankey Chart as a funnel, considering events related to a customer (contact, trial, subscription, renewal, and others). » Read more
Last update: Apr 11, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://learn.microsoft.com/dax/rank-function-dax