The RANK.EQ function returns the rank or position of a value in a list of numbers.
RANK.EQ(
value,
column
[, order]
)
- value: Value whose range is to be obtained. It can be any DAX expression that returns a scalar.
- column: Column from which to extract the range of value.
- order: Optional argument that specifies how to apply the ranges, from the lowest to the highest value or vice versa.
The RANK.EQ function returns an integer.
The column argument cannot refer to any column created with the ADDCOLUMNS, ROW, or SUMMARIZE functions.
If value is not present in column or is a Blank, the RANK.EQ function returns Blank.
The duplicate values receive the same rank (the highest of which they would receive if they received consecutive ranks), but the next value receives the rank it would receive if the aforementioned duplicate values had received different ranks. That is, if a value appears twice and receives rank 1, the next value will receive rank 3.
If order takes the value 0 or False (default value), the ranges are applied from highest to lowest. That is, the highest value will receive rank 1. On the contrary, if order takes the value 1 or True, the ranges are applied from lowest to highest, which implies that the lowest value will receive rank 1.
In this first example we start from the following table ("Data"):
If we wanted to add a calculated column with the range of each number in the Value column, we could use the following expression:
Posición = RANK.EQ(Data[Valor], Data[Valor])
We see that the value 8 (the highest) has received rank 1, 7 has received rank 2, etc. The value 2, included twice, does not receive ranks 5 and 6, only 5. But the next value, 1, receives rank 7.
In this second example we start from the following data table ("Data2"):
We want to add a calculated column in which the range of each value in the Value column is dumped taking the Reference column as a reference, for which we use the following code:
Posición = RANK.EQ(Data2[Valor], Data2[Referencia])
The result is shown in the following image:
This time, the number 8 is not present in the Reference column, so the RANK.EQ function returns Blank for this value. The value 7 is in the first position of the Reference column (ordered from highest to lowest) and receives the rank 1. The number 5 is in the third position (after 7 and 6) and receives the rank 3, and so on.
