DAX Fridays! #9: RANKX DAX Function

[@mnowako]

I can't belive how quickly I've mastered RANKX(). Many thanks Ruth!!!

[@CurbalEN]

Only with my videos? Either way congrats!! I know the feeling :) /Ruth

[@SamirHabib]

I came here to learn RANKX, but as bonus I've learned ALLSELECTED also. I knew about ALLSELECTED, but now I know where it would be usefull. Thanks RUTH my best teacher!

[@CurbalEN]

Pleasure to be your wing - woman! /Ruth

[@decentmendreams]

I tried five videos to help me with rankings with no success. Yours worked. Sweet!

[@CurbalEN]

🥳🥳 /Ruth

[@michaelcarvajal4563]

You show the way to build upon previous functions to make sense of a more complex solution (measure) in a very clear way. Thank you.

[@CurbalEN]

:) /Ruth

[@azizquazi]

I am revisiting this video for a refresher. This time my DAX knowledge has improved bit compare to my first visit. What a wonderful video to understand RANKX function. Thanks @Ruth for making my life easy. Cheers..

[@CurbalEN]

Wonderful to hear and thanks for sharing! /Ruth

[@meghasyam427]

I understood 2 concepts rankx and allselected very easily and quickly. Thanks a lot ruth.

[@edududu504]

Hey thanks for the master class! I've been trying to use this function based on PBI documentation without any successful results for hours, then I watched your video and in 5 minutes the work is done!!!

[@CurbalEN]

+Eduardo Orihuela Really Happy to hear that :) That is exactly the reason why I make this videos!! Thanks for sharing :) /Ruth

[@tymothylim6550]

Hello! Thank you again for the video. Your videos have been a blessing! I enjoyed learning about ALLSELECTED(). Using the slicer helped me better understand this, and how it can be used with RANKX().

[@zubairso]

Been watching your videos every now and than for the functions that I need to use but now I've made a habit of watching at least two videos everyday from the playlist and couldn't be happier!

[@CurbalEN]

Great practice! :)

[@martinajuvara6622]

Thank you Dax Fridays! So clear and helpful: Dax is a killer - sometimes behaving in unexpected ways when a report is due. Like others, I am now going back to basics and started at Dax Friday #1. I am sure I will thank Ruth next time I need to rush something out and know what to do.

[@CurbalEN]

Fingers crossed!!

[@NorbertoVeraReatigaNVR]

Ruth: Excelente explicación, como siempre deja muy claro los posibles usos de las medidas relacionadas al caso, estaré siempre atento a sus publicaciones!! Muchas Gracias!!!

[@CurbalEN]

+Norberto Vera Reatiga Muchas gracias por todo tu apoyo Norberto! Es un verdadero placer saber que mis vídeos te son útiles. Que tengas un buen fin de semana! /Ruth

[@gregprovin5866]

Excellent video! You seem to have covered the most common issues that one might run into. Thank you! Tack!

[@ymohamed3154]

Thank you for the nice explanation. I have a remark, if we sort from small number to large number (ie in DAX instead of "1" we write "0"), for example, the rank is not shown correctly in a chart. I would be very grateful for your answer.

[@greghodgkinson9580]

Thank you for this video. You helped me solve an issue with blanks showing up the the code IF(NOT(ISBLANK solved my issue. Thank you again Ruth for your video's they are a great source to learn DAX.

[@CurbalEN]

Glad to hear! /Ruth

[@praveenkakumanu919]

you work is Awesome Ruth it really good so Thanks for you

[@katbiernikowicz]

you are a dax killer! My RANKX finally works!!!!! Thanks :):):)

[@CurbalEN]

👏👏👏 well done!!

[@ravsha85]

Ruth your teaching has been awesome and it helps a lot for beginners like me, I do have a question in this video. What difference does it make when the rankx filters by Table and by a column ?

[@CurbalEN]

+Ravi Shankar Hi Ravi, thanks! I don't understand what you mean? Can you explain what you are trying to do? /Ruth

[@imranbuhary]

Could you please teach, e.g. when we created the rank on "Product Name" and when creating the table we want to show both "Product Name" and "Product Number" ? how to keep the same ranking because its just additional information and not a filter.

[@CurbalEN]

To get fast suppport with your question, I recommend you to post in the power bi community. /Ruth

[@imranbuhary]

@@CurbalEN Okay.

[@deepakagrawal465]

Excellent. Would be great if you can cover the following common business scenarios: 1. TOPN, UNION 2. Budget incorporation with Actuals at different granularity 3. Pareto Classification 4. MTD, QTD, YTD as filters using scenario tables 5. Multiple fact tables 6. Handling inactive relationships 7. Many to Many with DAX

[@CurbalEN]

+Deepak Agrawal Hi Deepak, next week videos I will talk about inactive relationships (Monday), many to many ( Wednesday) and sort of topN ( Friday), the rest I put on my to-do list. Thanks for the feedback! /Ruth

[@deepakagrawal465]

Curbal Wow...thanks a lot!! :)

[@CurbalEN]

+Deepak Agrawal Thanks to you for the feedback, it helps me a lot! Have a great weekend, /Ruth

[@liang-tsairlin7053]

Thank you for great videos. I've a question: Why do you need to use "ALL" function here?

[@giri41]

Hi Ruth, business scenario is they want categories, product both in the table when Rank.. could you please advice how the formula changes... important that when they filter on categories the product rank should not change

[@manasac2482]

Very informative.. in my case im using a matrix with a three level hierarchy for Company, Company type and Code. How do i see rank with going each level down in the hierarchy ? (using your current dax will only get me rank for one level )

[@Lovehurtsbutwhystill]

Thank you so much! I've searched a lot but only you demonstrated the Duplicates in Rankx!!!

[@CurbalEN]

Yey!! Glad it was useful :) /Ruth

[@SochBhai]

Hi Ruth, Thank you for this awesome series. I have a quick question for you. What is the difference between RANKX(Categories,[Total Sales],,0) and RANKX(All(Categories),[Total Sales],,0) when we have not applied any filter?

[@AjayKumarparmar]

Greetings from india Curbal, i have a question. if selection a column name gives correct ranking then why we even need to change it to a table name and then prefix ALL Selected function. Would it not be better just refer column name with ALL Function as you also saw in this video , giving column name works fine when it comes to ranking

[@akshayachandrasekar8285]

What does all(categories) do? Does it remove all the filters

[@naramarts1529]

Many thanks for this video Ruth. Its been extremely useful. I have a question. I have 2 columns that I will like to rank - based on Total Sales and Profitability. How do i go about creating the measure? For example, I have 4 Products A,B, C and D. The first 3 products have Sales of $6000 each while Product D has $8000. The Profits are different for each products. eg Product A has $1200, Product B made a loss of -$600, Product C has a profit of $900 while Product D has a profit of $2000. The ranking that I would like to create is to show Product D as 1, Product A as 2, Product C as 3 and Product B as 4. ( Note Products A,B & C have similar sales value and Profits are different while Product D has both high sales and profits and my ranking considers both Sales and Profit columns) I hope my example is clear. Many thanks

[@tuyoexcelypowerbi]

Hi, Do you have any example to avoid raning the total line? I saw how to do it with "IF" and "HASONEVALUE" but it does not work properly when one item is only selected in Pivot Table

[@andreslonardi5401]

Hi Ruth! I have one question for you. How do you suggest me to build a table which ranks the top 10 clients by sales of a sales table, and also show a final single row in the table which group all the sales of all the "others clients" not included in the top 10 listed rows above? The sum of the last single row will probably be higher than more than many of the ranked top 10 clients. Thanks a lot!

[@CurbalEN]

That is a fun challenge! How about post it in the power bi community? I am sure somebody has done it already :) /Ruth

[@bradleyroy1220]

Heck Yea, been working on a Rankx solution for over an hour, found the answer in this video!

[@CurbalEN]

+Roy Bradley Awesome! Enjoy the rest of your Sunday :) \Ruth

[@aperez1969]

Gracias, me ayudó a entender un poco más el funcionamiento de RANKX.

[@AD-pf4lw]

Can you show us how to draw a Pareto 80:20 graph using this info?

[@CurbalEN]

Follow this tutorial: m.kzbin.info/www/bejne/ml6tnGSgZ6xmpNk /Ruth

[@EstebanSuarezCastro]

Hi Ruth, how can I avoid duplicate ranks when some total values are the same? Thnks!

[@CurbalEN]

Here is one way if you don’t care about the order: m.kzbin.info/www/bejne/oYPXnpVum9msh8U /Ruth

[@EstebanSuarezCastro]

Curbal wow! You are Power BI black belt Ruth, helps me a lot! Love your channel, thank you! Keep going with that purpose!

[@EstebanSuarezCastro]

Curbal I solve it in excel like this, if you can write something similar in excel www.extendoffice.com/documents/excel/2512-excel-unique-rank.html

[@CurbalEN]

Thanks for sharing! /Ruth

[@HarshNathani]

Hi Ruth, Why does RANKX(ALL(Categories, ,Order_Details[Total Sales],,DESC) and RANKX(Categories,Order_Details[Total Sales],,DESC) give different results? ALL is used to REMOVE all Filters, but here we have no filters applied, then why are we using ALL(Categories) in the formula?

[@ngocnguyen4616]

Please show me how to use Rankx ascending with Blank

[@juanignacioXI]

I just applied it in my report, thanks to you Ruth!!

[@CurbalEN]

🥳🥳 /Ruth

[@gospelmoto2833]

Awesome! newbie here. Valuable!

[@nahilsolis9990]

I would like to know what happen if 2 products have the same value in total sales. for example : genen shouyu 1785 and guia malacca 1785.i have this problem on power bi. i will hope your answer. thanks for you help !

[@CurbalEN]

Hi Nahil, Depending on your requirements, this might help: m.kzbin.info/www/bejne/ppnSlolthrl1oLs /Ruth

[@MsLegendlime]

Great video!

[@carourena3730]

Hi Ruth, What is I want to keep ProductName and CategoryName columns visible in the visualization/table and rank 1-9?

[@supersayan9888]

very good video. Why is it nessary to use the ALL or ALLSELECT in rankX? Why cant you use rankx(tablename,...) it does not generate an syntax error. anyone?

[@rrrprogram8667]

Great .. Thanks ruth.... I owe u a treat.... :)

[@CurbalEN]

+RRR program You welcome! You already treat me with your support here, thanks! /Ruth

[@akashragi2347]

If I wanted to skip the ranking for blank category names , how can I do that ?

[@daniellopez46]

how do I rank all items except two on my list. I have survey data. Each person has multiple rows in my data; one per item answered per person. In my pivot table I have [Items] in rows and want to return blank on two values and rank all remaining items. I have an example btw but I posted a detailed example with my dax attempt and links to more info on and my comment disappeared.

[@nehaneha6]

I used the same formula on my own dataset, however the ranking doesn't look right. Not sure what am I doing wrong?

[@CurbalEN]

Can you post a sample model on the power bi community? Give as many details as possible to get help. /Ruth

[@107366]

Hi Ruth ; I have created SQL view from our ERP programme and retrieve to Power BI.It shows total revenue of raw materials from our suppliers (Sum X function quantity x unit price) When i want to create rank measure,it wrongly worked.Formula : IF(HASONEFILTER(V_STOKLU_TUM_ALISLAR[STOK_KODU]);RANKX(ALLSELECTED(V_STOKLU_TUM_ALISLAR);[Ciro];;DESC)) [Ciro] means total revenue.Revenue of suppliers here are repeated because same items with different quantities and different price are bought on different dates. How should we avoid this problem ?RAND() does not work. Thank you in advance

[@icu2314]

Genius!! You saved me hours...

[@CurbalEN]

Perfect! Glad it helped :) /Ruth

[@alejandrogonzalezbueno8044]

¿Cómo se crea el tipo de Matriz del minuto 7:35? Muchas gracias!

[@CurbalEN]

Hi Alejandro, It is called stepped layout, check it pit here: docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual /Ruth

[@alejandrogonzalezbueno8044]

@@CurbalEN Muchas gracias!

[@CurbalEN]

Un placer :) /Ruth

[@dheerajmishra8765]

Amazing Teaching skills.

[@CurbalEN]

Thanks! /Ruth

[@forallyoutuber2724]

Thank you Ruth

[@whats_next_31]

I cant get the values right and I cant find which PBIX to download for Rankx. Please help

[@kasturi2666]

Can I get this Filtered NorthWind file that you are using here, for download? I have the complete Northwind database file, but it is good to have your filtered file, so that I can follow step by step.

[@CurbalEN]

+Kasturi G Hi Kasturi, the link was there in the description box, unfortunately a bit buried between all the other links. I have moved it up a bit :) /Ruth

[@kasturi2666]

Hi Ruth, Thanks for the link, but the file in the link is a completely different file. It is not the one you are using in this RANKX video. Can you please check?

[@CurbalEN]

Hi again! A bit embarrassing ...you got this week DAX Fridays! file :) I have now changed the link to the proper file, I checked it 5 times,.. it should be ok now! Many thanks for bringing this mistakes to my attention, it helps me provide a better experience with the videos and content!! /Ruth

[@kasturi2666]

Thanks Ruth! Its absolutely ok. You are doing such a great job. We appreciate all the effort you are taking into making these videos :)

[@CurbalEN]

+Kasturi G You guys are making it sooo much fun with all your feedback, so keep it coming! It is a great motivator ;) /Ruth

[@nawaazramjanee6903]

Hi Ruth, I've tried this but I'm getting an error of "Column cannot be found or may not be used in this expression". I'm using a whole number column, could you help please? Thanks

[@CurbalEN]

It could be a lot of things, post your question with sample data and model in the power bi community to get personalized help. /Ruth

[@narenderreddy2070]

HI I have implemented the above dax but surprisingly am getting rank 1 for all rows. rank camp = RANKX(ALL('ideas explore'),[ideas received],,0) ideas received = CALCULATE(COUNT('ideas explore'[ID])) ideas received is a measure. please explain.

[@CurbalEN]

I am afraid I can’t. Please post the question in the power bi community and follow this steps to get help quickly: m.kzbin.info/www/bejne/pYmpgJiYqtuFsJI /Ruth

[@lynxwomancat]

Hi, I'm just learning PowerBI. I have a Matrix table with Region, Location, Total Amount. I gave Region TOPN (3) but need to only list Top 3 of each location, by Region, by Total Amount. Would I write measures to do this or can I do multiple TOPN?

[@CurbalEN]

Hi Sandy, Could you please post your question in the Power BI Community ? Thanks! /Ruth

[@asierbarbarin1098]

The link to the file is down. Could it be fixed again? Gracias Ruth

[@CurbalEN]

+asier barbarin Sure! I will take a look this afternoon, I am on my mobile now! /Ruth

[@Technowolfopedia]

Thanks for the video on Rankx, I have a calculated table using summarize, This does a group by Month > Product and Issues for the product. I want to Rank the issues by the product by for the particular month. I want to get the top contributing issues for the particular product for the particular month. Can you help me with this.

[@CurbalEN]

Hi, That is a perfect question for the Power bi community! Follow this steps to get support quickly: m.kzbin.info/www/bejne/pYmpgJiYqtuFsJI /Ruth

[@tyjulianks]

Take the table including both CategoryName and ProductName columns and set total row visible, woBlanksCategoryRank measure returns right category ranking in the total rows but wrong product ranking while woBlanksProductsRank measure returns right product ranking but wrong category ranking the the total rows. Is a way to fix them to show the right product/category ranking in each column? By the way, in the downloaded file there are only 3 categories in the categories table, but in the slicer visualization there is one additional "(blank)" item appears. How to take it out from the slicers?

[@anushap5411]

rankx(ALL(VW_NAV[Fund_Name]),CALCULATE('VW_NAV'[Return_PTP]),,DESC,Dense) Hello. By implementing the above DAX , my rank is skipping by 1 when the values become negative ( eg - 1 2 4 5 6) . Can you pls help me

[@The_True_Aviation_Avgeek_6537]

is there anyway you can rank a text column in powerbi measure

[@CurbalEN]

Sure there is, please post your question in the power bi community to get detailed help. /Ruth

[@1989Bismillah]

Hi Ruth, when trying to follow your example I get the following error when choosing the column name: 'the value for 'column name' cannot be determined. Either 'column name' doesn't exist, or there is no current row for a column named 'column name''. I have checked and the column definitely exists and has values. Could you please help? Thank you

[@CurbalEN]

That usually happens when you create a measure instead of a calculated column, can you please check? /Ruth

[@1989Bismillah]

@@CurbalEN Hi Ruth, I checked and you're right, I did it with a calculated column and it worked perfectly, thank you !

[@CurbalEN]

Yey!! :) /Ruth

[@fatimaferreira3285]

Hi Ruth! Thank you for all the videos! Can I ask you something. I´m trying to define a rankx row by row, and the formula is working (RANKX(filter('PRODUCTION ORDERS';'PRODUCTION ORDERS'[Sequence_key]=EARLIER('PRODUCTION ORDERS'[Sequence_key]));'PRODUCTION ORDERS'[Sequence_S1];;)) but When I try to sort it ascending the formula loses the filter. Any tip? Cheers, Fátima

[@CurbalEN]

I thing that jumps out, can you please check in the power bi community? /Ruth

[@fatimaferreira3285]

@@CurbalEN I would love to, how can I do it? In your website?

[@CurbalEN]

Here: community.powerbi.com You will get help in no time! /Ruth

[@fatimaferreira3285]

@@CurbalEN Done! Thank you so much! Cheers, Fátima

[@CurbalEN]

My pleasure 😇 /Ruth

[@indranildey9384]

Hi, I am getting a gap in the rank when the value (based on which I want to rank) goes from +ve to -ve despite of using "Dense". SaleValue Rank 20 1 10 2 -10 4 -20. 5 Seek your help. Thank you, Indranil

[@CurbalEN]

Check this video: m.kzbin.info/www/bejne/d2qYn6SGgcmgiLc /Ruth

[@anushap5411]

Hi. We’re you able to find a solution for this ?

[@ashwinkarki8112]

Regwrding this wocolumnFilterwoBlanksProductRank how is this rank coming as 2,9,3,5,6,1? Can u help me how is this coming?

[@Dev_Bartwal]

If there is 2 value is same then whts is the rank. Example product1 value is $1000, and Product2 value is $1000 same as product1 So whts is the rank of both product?? Plz help RUTH

[@CurbalEN]

Here is an option, take a look: m.kzbin.info/www/bejne/oYPXnpVum9msh8U /Ruth

[@MarkHinderliter]

A McDonald's chocolate chip cookie has more sugar and fat per ounce than any other menu item. Thank you ranking function!

[@CurbalEN]

+Mark Hinderliter 😂 are you doing a report on McDonalds? Or creating a diet plan for someone? /Ruth

[@MarkHinderliter]

Hi Ruth! Nah, I'm just doing it for fun and pulled their nutritional data to practice with. It's a little daunting that you've got over 50 of these videos going...I'm gonna need rubber (steel?) bands to keep my head from exploding.

[@CurbalEN]

+Mark Hinderliter Show us when ready, could be. Really fun dashboard :) Yes..61 and counting... and I have only covered like 20% of all functions! If you have some bands over.. you know where to send them :) /Ruth

[@dariom5073]

Thanks Ruth!

[@CurbalEN]

You welcome! /Ruth

[@amarycycycy4560]

not able to download files

[@CurbalEN]

I have created a download center where you can get all my pbi files in one place. Here it is: curbal.com/donwload-center /Ruth

[@lalitkaushik1812]

How to get second highest value in sales

[@kyang7816]

thank you so much

[@maryamseidnejad3280]

It was excellent:)

[@ihebchafai2780]

Thx Ruth

[@rakkhnaka]

Why are we using ALL?

[@stevennye5075]

very infomrative

[@CurbalEN]

Glad you like it :) /Ruth

[@daniellopez46]

I just posted a detailed comment and it disappeared???

[@ayoubjeljeli1179]

Why did you put ALL(Categories), instead of Categories ??!

[@Лианна-х3б]

Dissapointed you didn't cover additional settings: Value. Can't understand how to use it.

[@CurbalEN]

The value part is optional, you don’t need it to make rank work, but yeah, I agree, I should have covered that too. /Ruth