ORDERBY
In a window function, the expressions, and their directions, that order each partition.
Group: Window and visual calculations. Compatible with DAX.
Syntax
ORDERBY ( <orderBy_expression> [, <order>] [, <orderBy_expression> [, <order>]] … )| Parameter | Type | Description |
|---|---|---|
orderBy_expression | Expression repeatable, evaluated for each row | A value worked out for each row of the relation: a column, or an expression such as a measure. |
order | Keyword optional, repeatable | ASC (when left out) for smallest first, DESC for largest first; 1 or TRUE, 0 or FALSE say the same. |
Returns
None. It gives no value of its own: it tells the window function around it how to order the rows.
Remarks
- It goes only inside
OFFSET,INDEX,WINDOW,RANK,ROWNUMBERand the visual calculation functions; anywhere else it is an error. - Each expression may have its own direction; the second decides between rows the first leaves tied.
- A measure in it is evaluated for each row of the relation, so
ORDERBY ( [Liters sold], DESC )orders products by their own sales. - Left out of a window function, the order is by all the relation's columns, smallest first.
Examples
By category, then dearest first
EVALUATE
ADDCOLUMNS (
ALL ( Products[Category], Products[Product], Products[Price per liter] ),
"Row",
ROWNUMBER (
ALL ( Products[Category], Products[Product], Products[Price per liter] ),
ORDERBY ( Products[Category], ASC, Products[Price per liter], DESC )
)
)
ORDER BY [Row]| Products[Category] | Products[Product] | Products[Price per liter] | Row |
|---|---|---|---|
| Beer | IPA | 64 | 1 |
| Beer | Pils | 38 | 2 |
| Beer | Lettøl | 30 | 3 |
| Cider | Cider | 52 | 4 |
A number for the direction
0 is DESC: the largest first.
DEFINE
TABLE Shops =
DATATABLE (
"Shop", STRING, "Liters", INTEGER,
{ { "A", 50 }, { "B", 80 }, { "C", 80 }, { "D", 20 } }
)
EVALUATE
ADDCOLUMNS (
Shops,
"Rank", RANK ( DENSE, Shops, ORDERBY ( Shops[Liters], 0 ) )
)
ORDER BY Shops[Shop]| Shops[Shop] | Shops[Liters] | Rank |
|---|---|---|
| A | 50 | 2 |
| B | 80 | 1 |
| C | 80 | 1 |
| D | 20 | 3 |
Related functions
PARTITIONBY, MATCHBY, RANK, OFFSET, WINDOW.