ROWNUMBER
The current row's number within its partition in an order, unique to it.
Group: Window and visual calculations. Compatible with DAX.
Syntax
ROWNUMBER ( [<relation or axis>] [, <orderBy>] [, <blanks>] [, <partitionBy>] [, <matchBy>] [, <reset>] )| Parameter | Type | Description |
|---|---|---|
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, FIRST or LAST, as for RANK. |
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 place in its partition: 1, 2, 3 ..., never shared.
Remarks
- Unlike
RANK, rows that tie in the order get different numbers: the relation's other columns decide between them. - Where the context leaves no single current row, as on a total, the result is BLANK.
- Give the relation or leave it out; an empty first argument is an error.
- As with
RANK, the relation needs at least one model column, or must be a table the query defines.
Examples
Ties get their own numbers
DEFINE
TABLE Shops =
DATATABLE (
"Shop", STRING, "Liters", INTEGER,
{ { "A", 50 }, { "B", 80 }, { "C", 80 }, { "D", 20 } }
)
EVALUATE
ADDCOLUMNS (
Shops,
"Row", ROWNUMBER ( Shops, ORDERBY ( Shops[Liters], DESC ) ),
"Rank", RANK ( SKIP, Shops, ORDERBY ( Shops[Liters], DESC ) )
)
ORDER BY [Row]| Shops[Shop] | Shops[Liters] | Row | Rank |
|---|---|---|---|
| B | 80 | 1 | 1 |
| C | 80 | 2 | 1 |
| A | 50 | 3 | 3 |
| D | 20 | 4 | 4 |
Products numbered by liters sold
EVALUATE
ADDCOLUMNS (
VALUES ( Products[Product] ),
"Liters", [Liters sold],
"Row", ROWNUMBER ( ALL ( Products[Product] ), ORDERBY ( [Liters sold], DESC ) )
)
ORDER BY [Row]| Products[Product] | Liters | Row |
|---|---|---|
| Pils | 1004326 | 1 |
| IPA | 331884 | 2 |
| Cider | 301074 | 3 |
| Lettøl | 169179 | 4 |