DAX Fridays! #9: RANKX DAX Function

Подписаться 135 тыс.

Просмотров 81 тыс.

50% 1

Learn how to use the DAX RANKX() function to arrange your data based on criteria that you specify.
Keynotes:
00:00 Intro
03:00 Rankx on categories
03:17 Rankx ascending or descending
03:35 Rankx with allselected
05:28 Rank with blanks 05:28
07:26 Rank on products 07:26
10:50 Rank with column 10:50
Link to file: curbal.com/blog/glossary/rank...
PREVIOUS VIDEO: • DAX Fridays! #8: CALCU...
NEXT VIDEO: • DAX Fridays! #10: RANK...
Looking for a download file? Go to our Download Center: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
► Linkedin: goo.gl/3VW6Ky
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP

Опубликовано:

5 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 146

@mnowako 6 лет назад

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

@CurbalEN 6 лет назад

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

@tymothylim6550 4 года назад

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().

@michaelcarvajal4563 4 года назад

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 4 года назад

:) /Ruth

@decentmendreams 4 года назад

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

@CurbalEN 4 года назад

🥳🥳 /Ruth

@zubairso 3 года назад

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 3 года назад

Great practice! :)

@gregprovin5866 3 года назад

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

@azizquazi 4 года назад

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 4 года назад

Wonderful to hear and thanks for sharing! /Ruth

@edududu504 7 лет назад

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 7 лет назад

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

@meghasyam427 10 месяцев назад

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

@Lovehurtsbutwhystill 5 лет назад

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

@CurbalEN 5 лет назад

Yey!! Glad it was useful :) /Ruth

@martinajuvara6622 3 года назад

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 3 года назад

Fingers crossed!!

@juanignacioXI 4 года назад

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

@CurbalEN 4 года назад

🥳🥳 /Ruth

@SamirHabib 4 года назад

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 4 года назад

Pleasure to be your wing - woman! /Ruth

@NorbertoVeraReatigaNVR 7 лет назад

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 7 лет назад

+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

@aperez1969 3 года назад

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

@greghodgkinson9580 4 года назад

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 4 года назад

Glad to hear! /Ruth

@praveenkakumanu919 3 года назад

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

@MsLegendlime 2 года назад

Great video!

@gospelmoto2833 8 месяцев назад

Awesome! newbie here. Valuable!

@forallyoutuber2724 Год назад

Thank you Ruth

@katbiernikowicz 2 года назад

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

@CurbalEN 2 года назад

👏👏👏 well done!!

@icu2314 6 лет назад

Genius!! You saved me hours...

@CurbalEN 6 лет назад

Perfect! Glad it helped :) /Ruth

@bradleyroy1220 7 лет назад

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

@CurbalEN 7 лет назад

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

@rrrprogram8667 7 лет назад

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

@CurbalEN 7 лет назад

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

@dheerajmishra8765 4 года назад

Amazing Teaching skills.

@CurbalEN 4 года назад

Thanks! /Ruth

@dariom5073 5 лет назад

Thanks Ruth!

@CurbalEN 5 лет назад

You welcome! /Ruth

@kyang7816 6 месяцев назад

thank you so much

@maryamseidnejad3280 3 года назад

It was excellent:)

@SochBhai 5 лет назад

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?

@ihebchafai2780 4 года назад

Thx Ruth

@tuyoexcelypowerbi 4 года назад

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

@deepakagrawal465 7 лет назад

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 7 лет назад

+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 7 лет назад

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

@CurbalEN 7 лет назад

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

@manasac2482 3 года назад

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 )

@ravsha85 7 лет назад

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 7 лет назад

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

@carourena3730 4 года назад

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

@ymohamed3154 2 года назад

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.

@stevennye5075 5 лет назад

very infomrative

@CurbalEN 5 лет назад

Glad you like it :) /Ruth

@AjayKumarparmar 4 года назад

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

@lynxwomancat 6 лет назад

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 6 лет назад

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

@naramarts1529 3 года назад

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

@daniellopez46 3 года назад

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.

@Technowolfopedia 5 лет назад

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 5 лет назад

Hi, That is a perfect question for the Power bi community! Follow this steps to get support quickly: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-oXFOebuvRz0.html /Ruth

@tyjulianks 7 лет назад

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?

@andreslonardi5401 6 лет назад

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 6 лет назад

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

@anushap5411 2 года назад

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

@liang-tsairlin7053 2 года назад

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

@HarshNathani 4 года назад

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?

@giri41 3 года назад

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

@akshayachandrasekar8285 2 года назад

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

@narenderreddy2070 5 лет назад

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 5 лет назад

I am afraid I can’t. Please post the question in the power bi community and follow this steps to get help quickly: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-oXFOebuvRz0.html /Ruth

@nahilsolis9990 6 лет назад

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 6 лет назад

Hi Nahil, Depending on your requirements, this might help: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-phoeV7QTBjY.html /Ruth

@107366 4 года назад

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

@whats_next_31 4 года назад

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

@akashragi2347 2 года назад

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

@fatimaferreira3285 5 лет назад

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 5 лет назад

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

@fatimaferreira3285 5 лет назад

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

@CurbalEN 5 лет назад

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

@fatimaferreira3285 5 лет назад

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

@CurbalEN 5 лет назад

My pleasure 😇 /Ruth

@MarkHinderliter 6 лет назад

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

@CurbalEN 6 лет назад

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

@MarkHinderliter 6 лет назад

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 6 лет назад

+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

@asierbarbarin1098 7 лет назад

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

@CurbalEN 7 лет назад

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

@nawaazramjanee6903 4 года назад

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 4 года назад

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

@TheTrueAviationAvgeek6537 5 лет назад

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

@CurbalEN 5 лет назад

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

@1989Bismillah 5 лет назад

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 5 лет назад

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

@1989Bismillah 5 лет назад

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

@CurbalEN 5 лет назад

Yey!! :) /Ruth

@supersayan9888 3 года назад

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?

@Dev_Bartwal 6 лет назад

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 6 лет назад

Here is an option, take a look: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kRtmb8ftyQc.html /Ruth

@imranbuhary 5 лет назад

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 5 лет назад

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

@imranbuhary 5 лет назад

@@CurbalEN Okay.

@ashwinkarki8112 3 года назад

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

@indranildey9384 4 года назад

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 4 года назад

Check this video: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-A95nqPLdmRU.html /Ruth

@anushap5411 2 года назад

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

@nehaneha6 4 года назад

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

@CurbalEN 4 года назад

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

@EstebanSuarezCastro 5 лет назад

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

@CurbalEN 5 лет назад

Here is one way if you don’t care about the order: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kRtmb8ftyQc.html /Ruth

@EstebanSuarezCastro 5 лет назад

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

@EstebanSuarezCastro 5 лет назад

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 5 лет назад

Thanks for sharing! /Ruth

@kasturi2666 7 лет назад

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 7 лет назад

+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 7 лет назад

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 7 лет назад

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 7 лет назад

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 7 лет назад

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