Compute a ranking based on multiple columns

[@joaorataoo]

So well explained that it seems easy. I feel emotional, like a child discovering something new, when I see these explanations.

[@blaisehara8337]

I'm still wrapping my head around how this worked, but this saved my life! Thank you so much!

[@joaquimcosta952]

With all due respect, ALBERTO is a DAX poet! The quality of the code is truly impressive ... The least I can do to show my thanks it’s make “LIKE”.

[@DanielADamico]

You guys are unbelievable! Thanks for sharing!

[@EricaDyson]

So elegantly explained. I got it all (amazed myself).. all thanks to you.

[@johnknapowski7494]

Thanks a ton, had to make a running sum calculation, and needed to first index rows based on two columns. Once I ranked each (one a date field with duplicate dates) the other a unique text field, your shifting trick worked great!

[@phoenixflower1225]

Best explanation ever ! - Thank you!

[@pilarbaldominos2505]

Thanks for sharing! It is amazing how you can explain in an easy manner a very complex problem!

[@pravinupadhyay2046]

While seeing your videos ,I always feel like the Spirit of Dax itself is talking to me and explaining how it works.

[@sands7779]

Spirit of dax - sometimes I see it, sometimes I don't. Probably something I missed at first principles stage.

[@grazielleps4244]

Thanks a lot for your time and the great explanation!

[@samaguire1]

Wicked video. Well done Alberto! So we'll explained.

[@ed2921]

Brilliant Alberto, nice work!

[@raitup00]

Amazing way to solve this problem!!

[@sands7779]

Useful clear video the way it explained from first principles you might have tried with calculated columns to creating virtual table in dax for more dynamic ranking based on filters applied by user.

[@sumit-visatotravel8780]

It would take sometime for me to understand the flow for the ranking based on measures. But this is exactly the problem I am facing in my current work assignment. It would interesting to implement this. Thanks and as usual great, clear explanation.

[@henrifanda4784]

you are a life saver. thanks so much.

[@picious]

always increasing the bar !!! respect

[@SAULSM17]

Tu inglés es super claro!!!

[@nikakalichava8012]

thanks ! this is what i was looking for!

[@TECHOICEAB]

Very useful video !!!

[@thierrybaniab4603]

Thanks you so much for this so well explained video. I just want to know the way i can use this ranking method (in mesure) to complete int the same way a running total (not base on date)

[@ДмитрийНикитин-и8о]

6:55 Не понятно, для чего в первом аргументе функции RANKX оборачивать таблицу 'Customer' в ALL? Функция RANKX не инициирует преобразование контекста, поэтому таблица 'Customer' не будет отфильтрована текущими значениями в столбцах таблицы.

[@rohithkothaneth]

Thanks for your detailed video. How to tackle if we have duplicates in the text column. My scenario is I have a text column let's say "Name", second column I need to do COUNT("Number"), here number is in TEXT format, and need to find the RANK Based on the count(number column). Could you please help me.

[@AlbertoGastaldo]

Hi Alberto, very interesting exercise :-). My only question is : Why did you use REMOVEFILTERS() instead of ALLSELECTED() when calculating the MaxCustomerCode variable ? Since we want to calculate rank for only visible customers, i was expecting to get the MAX of the current visible customers. Thanks for your clarification . Bye Alberto

[@SQLBI]

In this case REMOVEFILTERS is faster. The need for MaxCustomerCode is just to define a multiplier used to define unique values, it wouldn't be very useful to use ALLSELECTED and because you get a single value, using REMOVEFILTERS guarantees a faster execution.

[@raghavgupta6032]

Hi Alberto, TYSM for coming up with such practical use case. I would request you to please make a video on how can we rank based on 2 measures.. Thanks

[@SQLBI]

Already available here: www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

[@andreanikitacavaletti9761]

siete fortissimi!!! W SQLBI!

[@epinedoh]

Gracias nuevamente

[@2404Pepe]

Wow! thanks so much!!! great example

[@dbszepesi]

Alberto - thanks for the video. Can you explain what hardware/software you use to interact with the video via pen? I'd love to be able to do that over teams with my customers sometimes or for online presentations to user groups, etc.

[@SQLBI]

Hi Dan, In this video I use a Galaxy Tab 12" mirrored to the PC and then grabbed from there to enter the video. I write and draw on screenshot previously prepared. In other videos I write directly on the PC screen with Epic Pen, but I found that drawing on bitmaps is a smoother experience.

[@JosefineNord]

Great solution! It works. Though, I wish to make a card above, showing the rank for a single Selected Customer // based on all the customers in the group. In that case, your measure will always show me rank 1 // as there is only one customer in the context. Do you have an idea of how I can get this right?

[@argokusumandani9773]

Amazing,. Many Thanks Sir!

[@goldwhispers]

This is so great, solved my problem only problem is the calculated column works however the measure does not grrr... scratching my head here trying to figure it out!

[@PASSBIVC]

Thanks for the video!!!!

[@eversonjunior8494]

Hi Could you provide any example of TOPN on Line Chart using legend and date?

[@millyq3407]

Amazing, is in the way he explains, a born teacher for sure. Thank you for this! Anyone would know how to add to it so it capture Top 3 and Bottom 3? I tried but so far no luck.... :(

[@wmfexcel]

Thanks for the detailed explanation. But my head is still spinning for the third argument used in RANKX... Appreciate if there are more materials / videos on that. RANKX seems to be a simple function, but it is not. 😅

[@SQLBI]

By default it's the same argument as the second argument, it's relatively rare to need a different evaluation. See dax.guide/rankx/

[@asgerlarsen2083]

Awesome video! The ranking on the calculated column worked great for me, however, I run into a problem when ranking based on the measure. When I use rankx on my rounded sales measure, the ranking measure returns a lot of duplicate ranks even though both the rounded sales and customer code is different. What causes this?

[@SQLBI]

Check the example and try to reproduce it step by step with your model until you see where the difference comes from.

[@sands7779]

Like SqlBi said, check results for one different item ( say customer codes with same ranks in dax measure) step by step until you see where differences arise. Calculated column calculated at refresh, measure as report is filtered. Check are entire customer codes coming through and how they are ranked when table is sorted. Are there 2 rows for same customer number- should data be grouped more or more columns used for ranking. Customer codes may be text with 50 after 100. If customer code is numeric (sales x max customer code) + customer code should deal with that. if text you may need to pad with leading 0s to have 050 ranked before 100 if ascending order.

[@Vpatel169]

I have tired the same code in my but i get the odd Visual is exceeding limit

[@daniellopez46]

what if instead you wanted to leave 1-2 rows blank and then rank over the remaining items? How would you do that?

[@mshparber]

On what surface do you write?

[@SQLBI]

It is a tablet mirrored on the PC and then grabbed with OBS. I write on bitmaps on the tablet. As software, I used Squid in this video, even though I moved to Noteshelf in next ones, as it works a better for my needs.

[@hi_vishy]

Simply genius

[@abhishekstatus_7]

Thanks for the sharing this Alberto:) . I have one question if we can't see the data model or can't build the calculated column as well then how we are gonna achieve the same. Thank you!

[@SQLBI]

That is challenging and could have a huge impact in performance. However, if you can't see the data model, you probably can't create a measure, are you?

[@abhishekstatus_7]

@@SQLBI Yeah, that's true the problem is the reports are connected with Analysis service and they're not providing us to see thr data model and relationship both. ☹️☹️☹️

[@SQLBI]

Do you have an old version of Power BI? You can currently see the diagram view when you create a live connection with Power BI Desktop.

[@abhishekstatus_7]

@@SQLBI No, We don't have old version as we are working on organization laptops . We tried to create a measure but those measures aren't working. They have also created measures in MDX and they are asking us to work on those only which is not helping us to get the required results. We're in discussion with the team to help us for the access so that we can able to see the data model and implement the same...

[@SQLBI]

If the Analysis Services model is Multidimensional and not Tabular, you cannot see the data model - and the process of creating measures in DAX could be complex.

[@rajatsharma6088]

Hi Alberto, Thanks for the video. I am working on report to calculate 3 different rank metrics from 3 different fact tables to compare for a common dimension table. I was able to create measures for all 3. My main problem is that, I also have to calculate average of those 3 ranks for each row for the dimension table. All these need to be a Measure. Please advise how this could be achieved.

[@tangtom2478]

Hi, nice video. I still couldn’t understand the logic to address the @RoundedSales cannot be determined, is there any document I can refer to? Thank you!

[@SQLBI]

Please, can you clarify the question? The RoundedSales measure was defined just to show a case of ties using the standard sample model (Contoso) we use in all of our articles and videos.

[@tangtom2478]

@@SQLBI Thanks for replying me. My question is on the last issue we faced in the video, which is the “@RoundedSales cannot be determined”. Just wondering what’s the logic behind to address this error message. (I watched 3 times still couldn’t understand). Thanks!

[@SQLBI]

It is because how RANKX works. The third argument is by default copied from the second one, but in this case it is not in a valid context and you need to provide a valid expression there. See dax.guide/rankx

[@9845614859]

Sir nice explained of ranking But I have issue that instated of code I have region in the data . How to find region wise ranks in power bi Request u to explain the same

[@sauravsinha6939]

what if we have a Serial number of products and each serial number has lost of work orders which is string and each work order has closed dates how will you rank based on serial number , work order and date

[@mtavassoti]

can you show how to rank customers with all the same salds values with same rank number and with next rank being the next rank number rather than skipping duplicated rank numbers

[@bitips]

Hello Alberto, I would be very grateful if you help me with a challenge. I want to do a raking that takes into account four columns, however, three tables involved. Imagine that I want to have a table where the fields are (Emplyee_Name, Product_Category, Calendar year, Calendar month, [Total_Sales]]). In this table I want a Ranking by [Total_Sales] DESC. But, it needs to respect Date Filters on the page. I've been racking my brains over this for a week now and there is no material anywhere on the internet that teaches this. Can you help me with this, pleeeeeeeeease ?

[@romuloacd]

It look like magic. I have tried si much time rondó something like tris, and i never get the result.

[@HarshaVardhan-ti7nm]

There are chances of overlapping if u just take the max of a column. So I think taking a value at least 1 greater than the max value could avoid overlapping.

[@jdlopez131]

If customer code had negative numbers and also you wanted to arrange sales in descending and customer code in ascending order, this method wouldn't work. I hope you make a video where we need a different variation of this method.

[@excaliber4ps]

Dad guru!

[@NeumsFor9]

Good progressive disclosure

[@thanomnoimoh9299]

Your clip is good but I try to catch up on your English speaking. I think you should write a short description on screen while you explain.

[@SQLBI]

Did you try to activate subtitles?