DAX Statistical - RANKX function
Description
Returns the ranking of a number in a list of numbers for each row in the table.
Syntax
RANKX (<table>, <expression>, [<value>], [<order>], [<ties>])
Parameters
| Sr.No. | Parameter & Description |
|---|---|
| 1 |
table Any DAX expression that returns a table of data over which the expression is evaluated. |
| 2 |
expression Any DAX expression that returns a single scalar value. The expression is evaluated for each row of table, to generate all possible values for ranking. |
| 3 |
value Optional. Any DAX expression that returns a single scalar value whose rank is to be found. If omitted, the value of expression at the current row is used instead. |
| 4 |
order Optional. A value that specifies how to rank value, low to high, or high to low. ASC: Ranks in ascending order of columnName. DESC: Ranks in descending order of columnName. If omitted, default is DESC. |
| 5 |
ties Optional. An enumeration that defines how to determine ranking when there are ties. Skip: The next rank value, after a tie, is the rank value of the tie plus the count of tied values. For example, if five (5) values are tied with a rank of 8, then the next value will receive a rank of 13 (8 + 5). This is the default value when ties parameter is omitted. Dense: The next rank value, after a tie, is the next rank value. For example, if five (5) values are tied with a rank of 8, then the next value will receive a rank of 9. |
Return Value
If the parameter value is specified – returns the rank number of value among all possible values of expression evaluated for all rows of table.
If the parameter value is not specified - returns the rank number of the value of expression at the current row among all possible values of expression evaluated for all rows of table.
Remarks
If an expression or a value evaluates to BLANK, it is treated as a 0 (zero) for all expressions that result in a number, or as an empty text for all text expressions.
If a value is not among all possible values of expression, then RANKX temporarily adds value to the values from expression and re-evaluates RANKX to determine the proper rank of value.
Example
= RANKX (Sales,Sales[Sales Amount],,DESC)
