Introducing RANK window function in DAX
Vložit
- čas přidán 19. 06. 2023
- 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?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... - Věda a technologie
Alberto is by far the person I have learned the most from about DAX. Thanks for the great explanations!
The explanation as always easy to understand.👌
Perfect, as always. Thank you!
This is really useful. Thank You for all you are doing
Thank you! I solved my problem so easily with your video.
Very good explanation thanks
FINALLY really usable rank function
Thanks, @Alberto @SQLBI !
Very useful, thanks 🙂
Very practical! I’d much rather use this over RANKX. Thanks Alberto!!
Perfect and simple
You are awesome!
Awesome 👍👍👍
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.
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!
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.
If the table you provide to RANK is big, it could happen: the table must be materialized to complete the operation.
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.)
Rank is user friendly.
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.
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 ))
Alléluia !!
👏👏👏
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
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.
How can i use it on a card? As i select a filter it shows only 1.
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
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.
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.
ALLSELECTED restores the filters defined outside of the visual.
@@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.
If you use ALL then your ranking will be fixed.