Power BI Blog: RANK and ROWNUMBER (New DAX Functions)

Welcome
back to this week’s edition of the Power BI blog series.  This week, we inspect two new DAX functions, RANK and ROWNUMBER.

Two new functions have been added to
the DAX / Power BI repertoire that should assist when calculating rankings: RANK and ROWNUMBER are joining the DAX ranks.

These functions return a number
indicating the rank for the current context within the specified partition,
sorted by the specified order.  The
difference between RANK and ROWNUMBER is that if there is a tie (i.e.
two rows would get the same rank assigned) ROWNUMBER will return an
error, whereas RANK will just assign the same RANK multiple
times.  You should note that returning an
error is a last resort; ROWNUMBER will try to avoid doing that by
finding the least number of additional columns required to uniquely identify
every row and append these new columns to the ORDERBY clause. Only after
it is unable to uniquely identify every row, ROWNUMBER will return an
error.

These functions rely on the ORDERBYand PARTITIONBY functions.

In the following example, we have a
list of customers and their birth dates.  I have added the following measures to my
model:

This is the first part of the output:

All measures here return the same
result.  However, for customers that
share a birthday, the results are different:

Notice how both Donald Garcia and Kayla
Garcia are both on the same date.  Using RANK with the ties parameter set to SKIP (the default) assigns them a rank of
41.  The same happens when using RANK with the ties parameter set to DENSE.  However, notice that the next customer
receives a different rank (43 when the ties parameter is set to SKIP and
42 when set to DENSE).  By
contrast, ROWNUMBER gives Donald and Kayla an unique rank (41 and 42) as
it expands the ORDERBY clause to try to unique identify these customers
and is successful in doing so.

In the
meantime, please remember we offer training in Power BI which you can find out
more about here.  If you wish to catch up on past articles, you
can find all of our past Power BI blogs here.