We can also use DAX's RANKX function to create rankings based on measures. To demonstrate how this works, perform the following steps:
- Create the following measures:
Average = AVERAGE([Value])
Rank = RANKX(ALL('R04_Table'),[Average])
Rank2 = RANKX(ALL('R04_Table'[Category1]),[Average])
Rank3 = RANKX(ALL('R04_Table'[Category1],'R04_Table'[Category2]),[Average])
- Create a Table visualization and place the Item column, as well as the Average and Rank measures, in the Values area for visualization.
- Create a second Table visualization and place the Category1 columns, as well as the Average, Rank, and Rank2 measures, in the Values area for visualization.
- Create a third Table visualization and place the Category1 and Category2 columns, as well as the Average, Rank, Rank2, and Rank3 measures, in the Values area for the visualization.
These measures and tables emulate the functionality of ColumnRank, ColumnRank2, and ColumnRank3 just with measures instead of columns. The base measure, Average, is the measure by which we rank the rows within our table visualizations.
For the Rank measure, it is imperative that the ALL function, or a similar function, such as ALLSELECTED, be used. This is because the filter context of the visualization pre-filters the rows available to RANKX within the base table. Thus, without the use of ALL, the only row within the base table available for the RANKX function to iterate over would be those rows defined by the filter context of the visualization. In the case of the Rank measure used within the table containing the Item column, this would mean that without the ALL function to expand the filter context, all of the ranks would be 1 because only the current row would be available to the RANKX function to iterate over.
Similarly, with the Rank2 measure, because our second visualization groups rows by the Category1 column, we must use the column form of the ALL function to specify that all values of Category1 should be included when computing the rank. The same is true for the Rank3 measure, except that because both Category1 and Category2 are included in our third visualization, we must specify that all values of Category1 and Category2 be included when computing rank.
A failure to include the proper context for RANKX results in unexpected (incorrect) results in our second and third visualizations for Rank in the second visualization, and for both Rank and Rank1 in our third visualization.