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
-
SQLBI+ updates in May 2023
In 2023, we released the first draft of the Window functions in DAX whitepaper as part of SQLBI+. Since then, we have released a few updates and are now glad to announce the availability of the related 3-hour video course… » Read more
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://learn.microsoft.com/dax/rank-function-dax