RANK FUNCTIONS IN DAX

Microsoft defines DAX as a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel Data model. But what really is DAX? DAX or Data Analysis Expressions provides us with a set of functions which makes life a little easier while handling data in Power BI. DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. It helps you to create new information from data that already exists in your data model. You can create a measure or calculation using DAX formulas and reuse it in multiple places. The same measure can give you different results depending on the context in which you use it. This can help you analyze your data in-depth and obtain insights, which would otherwise be elusive.

DAX is a family of functions. These can be broadly categorized as:

• Aggregate functions

• Text functions

• Date and Time functions

• Statistical functions

• Filter functions

• Logical functions etc.

Let us look into a set of functions which are not talked about a lot, but which has a world of potential to help effectively analyze your data : the RANK functions.

Ranking functions helps us to find the ranking value of an element in the context of a column. There are 3 main ranking functions that we are going to look into and how it differs from one another, i.e.

RANKEQ

RANKX

RANK

The RANKEQ function returns the ranking of a number in a list of numbers. It is similar to the function with the same name in EXCEL. It is not widely used in DAX unless you are migrating an excel formula and helps to keep backward compatibility with Excel.

Its syntax is :

RANK.EQ(<value>, <columnName>[, <order>])

<value> argument can be a DAX expression that has to be evaluated

<columnName>is the name of an existing column against which rank will be determined

Often the same column is used for value and expression, meaning that you want to calculate the ranking of a column over itself. One scenario in which you might want to use a different column is when you have two tables, one with elements that you want to rank (for example, a specific group of products) and another with the entire set of elements to use for ranking (for example, the list of all the products).

Here we are considering the Contoso Sample Database and ranking the products according to their unit price for illustrating these functions,

Eg: RANKEQUNITPRICE = RANK.EQ('Product'[UnitPrice],'Product'[UnitPrice],DESC)

But here we can see that for the same unit price, the same rank is assigned, i.e. The next unit price is then given the next available rank, i.e. 5. So there is no way to resolve tied here. It is very similar to having all the students in an exam who scored the top marks given the first position. However, if there are 4 students who got top marks, then the next student is given only the 5th position instead of second.

Now let us look at the next rank function, i.e. RANKX. If you want to show the ranking value of an element according to a specific sort order, you can use the RANKX function.

Its syntax is :

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

Table: tablename

Expression: Any DAX expression that returns a single scalar value. The expression is evaluated for each row of table, to generate all possible values for ranking.

Value: Any DAX expression that returns a single scalar value whose rank is to be found. If value parameter is not there, then the value of the expression at the current row is used.

Order: A value that specifies how to rank value, low to high or high to low.

0 - descending -> high to low 1- ascending -> low to high

Ties: how to determine the order when there are ties. - skip and dense

Here the ties argument helps us to resolve the issue we saw with RANK.EQ function. This argument can take two values. Dense and skip. DENSE always returns the next rank value after a tie, whereas SKIP returns a value that skips as many values as tied values. So the above issue can be solved by giving ties as DENSE, so that we get 2 for the second rank. To go with our analogy, all the students with top marks are given rank 1, while the second student is given rank 2, in spite of how many students get 1st rank.

Eg : RANKXUNITPRICE = RANKX('Product','Product'[UnitPrice],,DESC,Dense)

The next function we are discussing is a filter function . It is the generic RANK function, which is relatively new. We are discussing it here as it can be used overcome some issues with the other two functions.

specific subject, for instance Math, we c'an specify that using order by.cified partition, sorted by the specified order. This function allows us to further rank the values of a column by a second parameter, which can be specified by the 'order by' clause. So, to go with our analogy, if we want to rate the students who got the top marks in our tests further by the marks of a

specific subject, for instance Math, we can specify that using order by.

RANK ( [<ties>][, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>] )

Ties: how to determine the order when there are ties. - skip and dense

Relation: A table expression from which the output row is returned.

orderBy(list of columns):An ORDERBY() clause containing the columns that define how each partition is sorted. This field can take more than one column names separated by commas.

blanks: how to handle blank values when sorting : DEFAULT, FIRST, LAST

partitionBy: A PARTITIONBY() clause containing the columns that define how <relation> is partitioned.

matchBy: containing the columns that define how to match data and identify the current row.

Eg : RANKUNITPRICE = RANK(SKIP,,ORDERBY('Product'[UnitPrice],DESC,'Product'[ProductName]))

In the above example, we further rank the products alphabetically based on their ProductName.

Conclusion

RANK.EQ, RANKX and RANK each of these functions have their own applications and drawbacks. A little practice into each of this can help us use these functions effectively to give insightful visualizations. Hope this little attempt will help you to explore more on these functions.