One of the key features of Power BI is the ability to perform complex calculations using DAX formulas. In this blog post, we will discuss two DAX functions, TopN and RankX, and explore the differences between them.
TopN
The TopN function returns a set of items from a table based on a specified column. The syntax for the TopN function is as follows:
TopN(N, table, [column], [order by column])
The first argument, N, specifies the number of top items to return. The second argument, table, is the source table from which the items will be returned. The third argument, [column], is optional and specifies the column used to determine the ranking of the items. If this argument is not specified, the first column in the table will be used. The fourth argument, [order by column], is also optional and specifies the column used to sort the items. If this argument is not specified, the items will be sorted in ascending order based on the [column] argument.
RankX
The RankX function returns the rank of each item in a table based on a specified column. The syntax for the RankX function is as follows:
RANKX(table, expression, [order by expression], [ascending/descending])
The first argument, table, is the source table. The second argument, expression, specifies the column used to determine the rank of each item. The third argument, [order by expression], is optional and specifies the column used to sort the items. If this argument is not specified, the items will be sorted in ascending order based on the expression argument. The fourth argument, [ascending/descending], is also optional and specifies the sort order. If this argument is not specified, the items will be sorted in ascending order.
Differences between TopN and RankX:
Purpose: The primary difference between TopN and RankX is their purpose. The TopN function is used to return a set of items based on their ranking, while the RankX function is used to return the rank of each item in a table.
Return Value: The TopN function returns a set of items, while the RankX function returns the rank of each item.
Sort Order: Both TopN and RankX functions allow you to specify the sort order of the items. However, the TopN function allows you to specify the sort order only for the top N items, while the RankX function allows you to specify the sort order for all items in the table.
Tiebreaker: If two or more items have the same rank, the TopN function does not specify how to break the tie, while the RankX function assigns the same rank to tied items and increments the next rank by the number of tied items.
In conclusion, both TopN and RankX functions have their unique uses in Power BI. The TopN function is best used when you want to return a set of items based on their ranking, while the RankX function is best used when you want to determine the rank of each item in a table.
For more information, you can check official documentation:
https://learn.microsoft.com/es-es/dax/topn-function-dax
https://learn.microsoft.com/es-es/dax/rankx-function-dax