Rankx Value

[Arul]

Hi,

Can anybody explain what is the exact use of "Value" argument in RANKX Dax function with example?

Thanks,

-Arul

[Greg_Deckler · Accepted solution]

@Arul Well, the way it works is that the value parameter allows you to find the rank of that specific value within all of the values returned by the expression parameter. For example let's say that you have a table with three rows with values of 1, 2 and 3. If you did this:

Rank Column = RANKX('Table', [Column], 4)

This would return 1 for all columns. Conversely:

Rank Column = RANKX('Table', [Column], 0)

This would return 4. If you left the value parameter out, then the value of expression at the current row is used instead so that would return 3, 2, 1 respectively for 1, 2, 3.

Why you would ever want to use this? I'm sure there is some use case out there somewhere.

[Arul]

@Greg_Deckler ,

Not quiet getting it. Could you explain it bit more with some examples?

Many a times we have left out this value argument, So I am wondering why value argument is there in RANKX function.

Thanks,

-Arul

[Greg_Deckler]

@Arul It is almost universally left out of RANKX formulas. I believe the concept behind it is that if you want to figure out the rank of a particular value within a range of values. For example, let's say that you have twelve months of aggregated, historical sales data and you wanted to determine the ranking of the most current month of sales data in comparison to this historical data. It almost certainly makes more sense used in a measure formula than for a calculated column but at the end of the day there aren't a ton of use cases for it in my opinion so it's hard to invent one. 

To perhaps add additional confusion, consider that these two statements are equivalent:

Column 2 = RANKX('Table', [Column2])

Column 3 = RANKX('Table', [Column2], [Column2])

In other words, when you leave out the value parameter, RANKX essentially inserts the current expression parameter in row context as the value parameter. 

You may be feeling why I created this: To *Bleep* with RANKX! - Microsoft Power BI Community