Blog Article & Copyright by Lukas Hillesheim
Internet articles usually show a simplified use of the RANKX function, in which only arguments 1, 2 and 4 of the 5 possible arguments are used. Therefore the purpose of argument 3 (value) may be unclear. The following article shows the effect of using argument 3 and how the internals of the RANKX function can be thought of.
1. Simplified Use of RANKX (Argument 1, 2, 4)
Based on a list of cities and their amount, a ranking is carried out:
Figure 1: DAX code to compute a list of cities along with their total amount and rank
Figure 2: List of cities
2. RANKX Internals
The workflow logic inside RANKX can be thought as follows:
Step 1 - Context. With the help of SUMMARIZECOLUMNS, a grid is built. SUMMARIZECOLUMNS was used in my example because many visuals generate dax code based on SUMMARIZECOLUMNS. SUMMARIZECOLUMNS creates a (filter) context for each city, which has a restrictive effect on both the [City] table and the [FactSales01] table.
Step 2 - Building the Lookup Array. For each city, the [Rank] measure is called. RANKX first creates a list of all cities (= argument 1). If the table [City] were referenced without further modification, the filter of the outer context would come into play and the expression "City" would return only one row or only a single city. Therefore, RANKX uses "ALL(City)" to ensure that this filter is removed. Thus, the expression "ALL(City)" returns the complete table [City], where there is a single row per city. Alternatively, you could also work with ALLSELECTED at this point to simulate the effect of a slicer on the table [City]. This additional task is omitted here, as it has already been solved in many internet articles.
Step 3 - Iteration. RANKX now iterates over the list of cities. For each loop pass, argument 2 of the RANKX function - "[SumAmount]" - is evaluated. [SumAmount], in turn, returns the total amount of the respective city. In the internals of the RANKX function, it may be specified that the value is added to an internal array holding the list of lookup values. A corresponding OOP code that represents this loop logic could look like this:
- Decimal[] lookupArray = {};
- ForEach ( Row r in City.Rows ) {
- lookupArray += TotalAmount(r[CityName]);
- }
Step 4 - Sort. After the list of city amounts has been created, it still needs to be sorted. After the sorting has been applied, the rank value can directly been derived from the index of each item. the highest city amount is located at index 0, the second-highest at index 1 etc. The rank value corresponds to the index of the element + 1. The OOP code for this could look like this:
- lookupArray.Sort()
Step 5 - Assign the Lookup Array to each Row. The lookup array contains the same values that we can see in the grid: { 18125, 10802, 9198 ... }. Since the content of the array is constant and does not change during the outer iteration, optimizing the engine will probably ensure that the array is physically generated only once. Logically, however, the lookup array is generated for each row, since the generation is part of the measure expression:
Figure 3: Lookup array per row
Step 6 - Evaluation of Argument 3. In step 6, argument 3 is evaluated. Since argument 3 was not specified by the caller, the parser takes argument 2 and substitutes argument 3 by argument 2:
Incomplete submission (without argument 3):
- MEASURE FactSales01[Rank] =
- RANKX(
- ALL(City), // Arg. 1 - Set
- [SumAmount], // Arg. 2 - Expression
- , DESC) // Arg. 4 - Sort order
Completion by the parser:
- MEASURE FactSales01[Rank] =
- RANKX(
- ALL(City), // Arg. 1 - Set
- [SumAmount], // Arg. 2 - Expression
- [SumAmount], // Arg. 3 - Value
- DESC) // Arg. 4 - Sort order
For line 3 with "Prague" this results in the following:
- - "Prague" is used to setup the filter
- - Argument 3 with [SumAmount] calculates a value based on this filter; in this case, 9198
- - The value 9198 is searched in the array and found at index 2
- - The rank value (2 + 1 = 3) is sent to the output
3. Passing a Constant Value
In the following, the measure definition is slightly changed and the constant value 9198 is used for argument 3. The array { 18125, 10802, 9198 ... } is created again as described above. Since the value 9198 is now found at index 2 for each city, the rank value 3 is mapped to each city.
Figure 4: Constant value 9198 for arg. 3
4. Passing NULL or BLANK()
The next test shows that - if you pass NULL value or BLANK() - the RANKX function will return the last rank value + 1. Since there are 8 elements in the array, the BLANK() value is given a rank of 9.
Figure 5: Constant null value for arg. 3
5. Non-exact Match
This example shows that a lookup-value that does not exist in the lookup array is first assigned to a range, and then the lower bound of the range is returned. In the example, the value 10801 is searched in the array and found in the range 10802-9198. Although 10801 is "very close" to the upper bound 10802, it is not the index of 10802 that is taken into account, but the lower bound of the range with the value 9189. Its index is 2 and the rank value 3 is returned.
Figure 6: Return lower bound
6. Out-Of-Range Value
Special cases of are values that are above the max value or below the min value. Values that are greater than the maximum are given a rank value of 1. Values that are less than the minimum are given the last existing rank value + 1. In the example, the constant value 100000 is passed, where the maximum value is 18125.
Figure 7: Out-of-range value
Summary - What are the arguments of RANKX responsible for?:
- - Arguments 1 and 2 are used to generate the lookup array
- - Argument 3 is used to match a search value against the lookup array
- - The other arguments control sorting and density