RANK

The current row's rank within its partition in an order; ties share a rank, skipping the next (SKIP) or not (DENSE).

Group: Window and visual calculations. Compatible with DAX.

Syntax

RANK ( [<ties>] [, <relation or axis>] [, <orderBy>] [, <blanks>] [, <partitionBy>] [, <matchBy>] [, <reset>] )

Parameter	Type	Description
`ties`	Keyword optional	`SKIP` (when left out): after two rows tied first, the next is third. `DENSE`: it is second.
`relation or axis`	Table optional	The rows it works among: a table of the model or one the query defines. Left out, the values the ORDERBY and PARTITIONBY columns have in the selection (as `ALLSELECTED` gives them). In a visual calculation, an axis such as `ROWS` instead.
`orderBy`	ORDERBY optional	`ORDERBY ( ... )`: how the rows of each partition are ordered. Left out, by all the relation's columns, smallest first.
`blanks`	Keyword optional	Where blank values go: `DEFAULT` sorts a blank just below zero among numbers and ahead of any text; `FIRST` or `LAST` put blanks at that end whatever the direction.
`partitionBy`	PARTITIONBY optional	`PARTITIONBY ( ... )`: columns of the relation that split it into groups; the function stays inside the current row's group.
`matchBy`	MATCHBY optional	`MATCHBY ( ... )`: the columns that identify the current row. Left out, all the relation's columns.
`reset`	Reset optional	In a visual calculation only: where the count starts again, as for `PREVIOUS`.

Returns

Whole number. The current row's rank in its partition: 1 for the first.

Remarks

Rows equal in every ORDERBY value share a rank.
Where the context leaves no single current row, as on a total, the result is BLANK.
The relation needs at least one model column, or must be a table the query defines with DEFINE TABLE; a table of new columns only, such as a DATATABLE in a VAR of the expression, is refused.
RANKX ranks a value among an expression's values; RANK ranks the relation's rows by an ORDERBY of one or more expressions, and takes partitions.
In a visual calculation, with an axis for the relation, it ranks the rows of each level among themselves.

Examples

SKIP, DENSE and blanks

B and C tie first. E has no liters: from the largest down a blank comes after the positive numbers; LAST puts it last in the smallest-first ranking too.

DEFINE
    TABLE Shops =
        DATATABLE (
            "Shop", STRING, "Liters", INTEGER,
            { { "A", 50 }, { "B", 80 }, { "C", 80 }, { "D", 20 }, { "E", BLANK () } }
        )
EVALUATE
ADDCOLUMNS (
    Shops,
    "SKIP", RANK ( SKIP, Shops, ORDERBY ( Shops[Liters], DESC ) ),
    "DENSE", RANK ( DENSE, Shops, ORDERBY ( Shops[Liters], DESC ) ),
    "Smallest first", RANK ( DENSE, Shops, ORDERBY ( Shops[Liters], ASC ), LAST )
)
ORDER BY Shops[Liters] DESC, Shops[Shop]

Shops[Shop]	Shops[Liters]	SKIP	DENSE	Smallest first
B	80	1	1	3
C	80	1	1	3
A	50	3	2	2
D	20	4	3	1
E	(blank)	5	4	4

Rank within the category

EVALUATE
SUMMARIZECOLUMNS (
    Products[Category],
    Products[Product],
    "Liters", [Liters sold],
    "Rank in category",
        RANK (
            DENSE,
            ALLSELECTED ( Products[Category], Products[Product] ),
            ORDERBY ( [Liters sold], DESC ),
            PARTITIONBY ( Products[Category] )
        )
)
ORDER BY Products[Category], [Rank in category]

Products[Category]	Products[Product]	Liters	Rank in category
Beer	Pils	1004326	1
Beer	IPA	331884	2
Beer	Lettøl	169179	3
Cider	Cider	301074	1

In a visual calculation

Each level ranked among itself: the quarters among the quarters, the years among the years.

DEFINE
    TABLE Visual =
        DATATABLE (
            "Year", INTEGER, "Quarter", INTEGER, "Liters", INTEGER, "All years", BOOLEAN, "All quarters", BOOLEAN,
            {
                { 2025, 1, 100, FALSE, FALSE }, { 2025, 2, 120, FALSE, FALSE }, { 2025, BLANK (), 220, FALSE, TRUE },
                { 2026, 1, 130, FALSE, FALSE }, { 2026, 2, 150, FALSE, FALSE }, { 2026, BLANK (), 280, FALSE, TRUE },
                { BLANK (), BLANK (), 500, TRUE, TRUE }
            }
        )
        WITH VISUAL SHAPE
            AXIS ROWS
                GROUP [Year] TOTAL [All years]
                GROUP [Quarter] TOTAL [All quarters]
                ORDER BY [Year], [Quarter]
            DENSIFY "Added"
    COLUMN Visual[Rank] = RANK ( DENSE, ROWS, ORDERBY ( [Liters], DESC ) )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
    "Rank", [Rank]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]

Year	Quarter	Liters	Rank
2025	1	100	4
2025	2	120	3
2025	(blank)	220	2
2026	1	130	2
2026	2	150	1
2026	(blank)	280	1
(blank)	(blank)	500	1

RANKX, ROWNUMBER, ORDERBY, PARTITIONBY, TOPN.

Last updated 3 November 2026 · OpenBI Studio 0.3.74