Introducing RANK window function in DAX

RANK is a new DAX function to rank items based on multiple columns: discover how RANK works and its differences with RANKX.
Article and download: sql.bi/801505?...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...

[@JC_Rosales]

Hi, I really love the way you explain things. I would like to ask how you would go about ranking on both levels. For example, I have a supervisor level and then an employee level, and I would like to rank the supervisor based on the combined performance and at the same time, rank the agents below on the second level.

[@davidm00ris]

Alberto is by far the person I have learned the most from about DAX. Thanks for the great explanations!

[@Ultramarplogic]

This is great, I fixed my RANKX functions with this ones BUT I have an issue that when the value is 0 it's giving me rankings out the total number of users, Example, I only have 100 players to rank for and it's ranking players with 0 goals in the 500 and 1000s, what is wrong with my measure (below)? For the record I intent to rank the players in each level (1-4), that's why I'm using the Allexcept. Measure = RANK( DENSE, ALLEXCEPT (PLAYERS,PLAYERS[LEVEL]), ORDERBY([Total GOALS], DESC, PLAYERS[NAME], ASC ))

[@vklemtz]

Hi, thanks for the video! I have a doubt if I want to rank based on a measure which is a multiplication of 2 measures. One is forecast for 3 months variance in quantity and the other is the variance in percentage (comparing snapshot from current to previous month). I have used rankx all selected with the ranking factor, which is the multiplication of these 2 measures. Even though my visual shows the correct values for variance, both in perc. and quantity, it does not rank correctly based on their multiplication. It gives me a sequence of 8,9,10,44, etc. Would you have any idea of what might be the issue, please? Thank you very much!

[@common_sense4753]

Thanks, @Alberto @SQLBI !

[@williamarthur4801]

I wonder if they'll let us power pivot users have it, we still have to use IF ( hasonevalue, etc, instead of selected. vary thorough article, as always, accompanying this video.

[@yoshiaaa100]

The explanation as always easy to understand.👌

[@mwaltercpa]

Very practical! I’d much rather use this over RANKX. Thanks Alberto!!

[@kingcobra071]

Thank you for the great tutorial. However, whenever I use it in my query, it says "Not Enogh Memory to complete this operation". Any suggestions would be highly appreciated. Thank you.

[@SQLBI]

If the table you provide to RANK is big, it could happen: the table must be materialized to complete the operation.

[@fahmywaleed]

Excellent content and explanation, one thing to improve if you can split the video into chapters so the viewer can skip back and forth in a convenient way. Thanks for the effort.

[@lionels839]

Alléluia !!

[@tmaris]

Perfect, as always. Thank you!

[@juanamarinzapata7357]

Thank you! I solved my problem so easily with your video.

[@JustRandomExplorer]

FINALLY really usable rank function

[@negares3971]

Thanks for your complete explanation. Would you please help me to write DAX Rank for this one, I want something like this table: Total | Year | Rank 300 | 2023 | 1 280 | 2023 | 2 250 | 2023 | 3 400 | 2022 | 1 360 | 2022 | 2 230 | 2021 | 1 Thanks A lot

[@gregoryoliveira8358]

Hi. This measure should help you: Medida = RANK( DENSE, ALL ( Tabela ), ORDERBY( Tabela[Total ], DESC), PARTITIONBY(Tabela[ Year ]) ) There is a video where they talk about the windows function. I was seeing it and remember your question.

[@souravkajuri]

This is really useful. Thank You for all you are doing

[@amandeepbasra8673]

Hi, how could we remove ranking for the months that we don't have sales. I have been trying to solve a similar problem but couldn't succeed

[@Bharath_PBI]

That's great, thank you. If RANK and RANKX are same in performance, except for use case, which would be preferred? X in the function name was an indicator for iterative functions (with few exceptions like FILTER etc.)

[@LearnAtHomewithGulshan]

Rank is user friendly.

[@gregoryoliveira8358]

I believe FILTER is a iterative function too, because it scanned all rows from the table parameter. And, if my mind is not becoming weak, I remember to listen our great SQLBI guys saying that it is an iterative function in their videos.

[@patrickm7760]

Very good explanation thanks

[@powrigan719]

Very useful, thanks 🙂

[@kawaiigaming2537]

How can i use it on a card? As i select a filter it shows only 1.

[@credencial7654]

Perfect and simple

[@marelyruiz1321]

You are awesome!

[@pepper_lab]

Awesome 👍👍👍

[@richardostrea7842]

👏👏👏

[@common_sense4753]

Hi, @SQLBI @Alberto. What would happen if we were to use All or Values instead of Allselected as the first argument for Addcolumns. I'm just not sure why we use Allselected here.

[@SQLBI]

ALLSELECTED restores the filters defined outside of the visual.

[@common_sense4753]

@@SQLBI Thanks so much! Yes, I did learn that indeed from your book, but I thought it was needed when I had a scenario where I explicitly wanted to preserve the external filters. Now, I see that it is a good practice to use it by default. Thanks, again.

[@LearnAtHomewithGulshan]

If you use ALL then your ranking will be fixed.