cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arul
Post Patron
Post Patron

Rankx Value

Hi,

 

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

 

Thanks,

-Arul

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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.
 
 

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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.
 
 

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@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

 

@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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.