DAX – comparison of a specific row with all rows of the table

This article describes how to compare all rows with the current one in a DAX calculation. As in this table. We have people who have the number of points, and our task is to find out what position they placed – who has the most points, finished in the first position, etc.

Before we begin the explanation, here are just two warnings:

• This calculation applies to the row context, so you will use it to calculate a new column (not a measure).
• This specific case could also be solved with the RANK.EQ function, but we are talking about an explanation of a principle that we could use for more complex calculations – see another example.

What is the problem?

You might think to start like this:

• = COUNTROWS(
     FILTER(
     ‘times’,
        …

but now comes the problem. You need to define that the Points value in the table we are going through must be higher than the Points value for a specific person. But how to distinguish these two “Points”?

We will use work with context:

• Position =
  var his_points = ‘times'[Points]
  var his_rank = COUNTROWS(
     FILTER(
     ‘times’,
        ‘times'[Points]>his_points))
  return
     his_rank+1

Why like this?

At the beginning we define the variable his_points, representing the points of the line for which we calculate the order. At the moment we are still in the line context, so for example we will get the number 98 for Betty.

We remember this value in the variable.

In the next variable, we will use the FILTER function to go through all the rows (the context is therefore extended to the whole table). However, the his_points variable has already been computed from before, so it works.

The calculation could be done a little differently – using the EARLIER function.

• Position earlier = COUNTROWS(
  FILTER(‘times’,
  ‘times'[Points]>EARLIER(times[Points])))
  +1