DAX Fridays! #10: RANK/ TOPN with slicer

In this video you will learn how to create a custom slicer that will rank your data based on the criteria that you specify, for example, Top 3, Top 5 and Top 10.
PREVIOUS VIDEO: • DAX Fridays! #9: RANKX...
NEXT VIDEO: • DAX Fridays! #11: Usin...
RANKX Tutorial: • DAX Fridays! #9: RANKX...
TopN file link: curbal.com/blo...
First we will create a ranking based on our data. Once we have the rank, we will create a slicer to filter by Top3, Top5 and Top10 results. Finally, we will connect the slicer to a measure to filter the results.
Keynotes:
Rank our data 01:30
Create a disconnected TopN slicer 03:20
Create a measure for the slicer 05:44
Create the measure that connects slicer to the table 07:30
Slicer returns data when nothing selected 09:13
Looking for a download file? Go to our Download Center: curbal.com/don...
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/...
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

[@MarkHinderliter]

Since I wasn't sure about downloading the source file, I generated dummy data to do the tutorial with, using this query in SQL Server: with CTE as ( select 1 as ID union ALL select ID +1 from cte where id

[@CurbalEN]

Mmmmm that is beyond my comprehension but creative people is the best I know!! :) Happy holidays and thanks for all your contributions here! Really appreciate it!

[@CurbalEN]

I will pin your comment, it will help somebody else :) /Ruth

[@MarkHinderliter]

That's just how I know how to do it. I'll bet you have a snazzier way to do it with generate series or some other abracadabra() function. =)

[@MarkHinderliter]

By the way, thanks goes to Mister James for supplying a good random function: theycallmemrjames.blogspot.com/2009/04/truly-random-number-in-ms-sql.html

[@CurbalEN]

😂😂 I should definitely try!! But next year, this year I am busy shoveling snow ❄️ 😂😂😂 /Ruth

[@musiliadebayo]

Hello Ruth, Thanks for all you do here, I am actually learning the underlying of DAX through the DAX friday and I actually used this TopN at work yesterday, it kinda felt like a walk in the park. Thanks again.

[@ruchigupta1536]

Ruth , I love all you videos :) my current dying need is that I want to have top N written on a drill through page. So the dill thru is already passing my selected filters, but now I need to apply top N on this drill thru visual and get the right numbers on one category , and importantly to export right rows in Excel. Thanks Look forward

[@shashindulakshitha3789]

Hi Ruth,Once again a nice video. Will u be able to give us such a nice video about the EARLIER function as well?? I found out, that you can do the looping events using the earlier function along with the ALL function,but im not very much aware about it. Thank you very much Ruth. Keep it up. Cheers!!!!!

[@CurbalEN]

+shashindu lakshitha Hi Shashindu, absolutely! Expect to see it in the coming weeks, it's on my to- do list now. Thanks for the suggestion:) /Ruth

[@jeffweir9358]

Hi Curbal. Nice vid. Instead of using MAXX to return something in the event that the TOPN slicer isn't selected, I think it might be more efficient leave it with BLANK(), and instead change your approach so thta the AvgMeasurementValue only gets triggered if the filter is applied, and otherwise defaults to just the unadulterated [AvgValue] measure. So use this pattern =IF(HASONEVALUE(TopTable[TopN]) , [Existing AvgMeasurementValue pattern], [AvgValue])

[@CurbalEN]

Thanks!! :) /Ruth

[@yearofmars]

I enjoy working through these videos. I think it looks better if you filter the entire table by the TopN slicer rather than just putting blanks in the column. You can do this by using a measure something like this: TopNXX = var SelectedN= if(HASONEVALUE(TopNTable[TopNValues]),SELECTEDVALUE(TopNTable[TopNValues]),max(TopNTable[TopNValues])) return switch(true(),SelectedN=0,1, [Rank]

[@CurbalEN]

Might be, but also my knowledge has increased with time too ☺

[@VinayKumar-ij4eu]

Awesome explanation of this function!! Thanks Ruth!!

[@CurbalEN]

🎉🎉

[@just4fun410]

Hi Ruth, Excellent video .We are using the same in our real time projects.Thanks a lot

[@CurbalEN]

+Vamsi Babu Hi Vamsi! Real time projects? Sounds like fun projects :) /Ruth

[@prasad9976]

Thank you for making such interactive example

[@zuhedagulmohamed5726]

Ruth, your videos are the best! the most amazing! Thank you.

[@CurbalEN]

🥳🥳 Thanks!

[@NorbertoVeraReatigaNVR]

Gracias, Ruth! muy útil este vídeo... como siempre, sorprendido gratamente!

[@CurbalEN]

+Norberto Vera Reatiga y muchas gracias a ti por todo tu apoyo compartiendo los vídeos en las redes sociales ;) Pasa un buen fin de semana, /Ruth

[@ranichennamma4436]

Hola Ruth, Pequeña sugerencia amistosa de la India, Cada vez que explique algo nuevo, puede tomar datos que todos puedan entender FÁCILMENTE sin perder demasiado tiempo en eso ... en otras palabras ... puede usar algunos datos de ventas o cualquier información que pueda comprenderse fácilmente. Las personas no deben tomar 5 minutos para entender deteniendo el video una y otra vez solo para comprender los datos de muestra. De lo contrario, tus videos son increíbles. La razón por la que estoy diciendo esto es ... está vaporizando mis niveles de entusiasmo. Raani Chennamma De la India

[@CurbalEN]

Buena sugerencia gracias :) /Ruth

[@deepakagrawal465]

Excellent. There is also a TOPN function in DAX which can be used along with SUMMARIZE to achieve the objective.

[@CurbalEN]

+Deepak Agrawal Isn't it amazing how many ways there are to do the same thing? :) /Ruth

[@deepakagrawal465]

Yes, Ruth. That's the beauty of this language. :)

[@CurbalEN]

+Deepak Agrawal Agree! :) /Ruth

[@ihebchafai2780]

Thanks Ruth. It's the simplest method i've been used

[@CurbalEN]

I like simple :)

[@ashokkumarbandaru4402]

Really it is very much helping me to Power BI queries Much Thanks you to share this kind of valuable videos to us

[@CurbalEN]

+ashok kumar bandaru You welcome! My pleasure ;) /Ruth

[@bradj229]

Cool video! Thanks Ruth!

[@pranjalagrawal4167]

Very well explained. Thank you

[@Rafian1924]

Simple and lucid explanation.

[@CurbalEN]

Thanks! /Ruth

[@Mlrossi1962]

I noticed that you popped into DAX studio to evaluate your expression. I would love to learn more about that as I have been struggling with an expression and was wishing I had a tool to step through my code. Do you have or would you be willing to create a walkthrough of DAX studio for this purpose?

[@CurbalEN]

Hi! Yes, i plan to do that on the next vertipaq series. Stay tuned! /Ruth

[@digantdesai007]

Hi, If you look at video, in initial video description, you can see Full Total is shown 10.56, irrespective of what you select in TOP N filter, however later in Video Total is coming based on your selection or even sometimes its not coming. Can you please let me know, how to get full total even though any TOP filter is selected. Regards Digant

[@CurbalEN]

+Digant Desai Hi Digant, Have you seen this video? m.kzbin.info/www/bejne/q5ergIKCmc6SoM0 /Ruth

[@nsrchndshkh]

Hi Curbal, Thanks for such great videos, Would you post video on how to separate "TOP N" and "Others" in pie chart. Thanks

[@CurbalEN]

Absolutely, but if you want a faster answer, try the power bi community :) /Ruth

[@tomash9785]

cool !! seen a few videos of yours. I will subscribe and hope to see more such valuable content

[@CurbalEN]

+Tomas Hujo Thanks for your feedback and subscribing Tomas! /Ruth

[@juanignacioXI]

Hi Ruth, awesome video, can we use Url images for that ranks?..for example salesmans tops with the photo of each worker. Thanks in advance!

[@alvarocondoricubillas9535]

Muchas gracias por compartir, me ayudó bastante!

[@CurbalEN]

Perfecto, me alegra oirlo !

[@chrisbaisako1258]

The 'Average Value' field seems to be an average value per machine, i.e. some group by machine calculation was done. Did the Average value = medianx(data, data[values]) statement do the grouping? I am referring to 1:31 of the video above.

[@miroslawakasperska8010]

Very interesting video - Thank you! Unfortunately I did not find here the answer to my problem. Could you point me to your video where you explain how to highlight one record in the table which is the ranking of e.g. cities depending on the chosen name in the slicer using DAX?

[@BaoNguyen-bm1gp]

Thank you for such a great video ! I have 1 question: What if I want to display the correct total for top 3, top 5, top 10 when selected in the slicer ?

[@akashtribhuvan8124]

Hi, Did you find an answer for the same, I am stuck, and want the SUM total only for the TOP N values. please let me know if you have any resolution.

[@LuisLopez-sc1qc]

gracias contigo aprendo mucho sobre medidas dax en power bi sigue asi con mas medidas gracias y bendiciones

[@CurbalEN]

+LUIS ALBERTO LOPEZ CHAVEZ Muchas gracias Luis Alberto, seguiré hasta que me pidáis que pare ;) /Ruth

[@LuisLopez-sc1qc]

lala sigue no mas q el conocimeinto no puede parar :)

[@CurbalEN]

+LUIS ALBERTO LOPEZ CHAVEZ 😂 /Ruth

[@jormaryjackson812]

Hello! It is a very good video! I like a lot! But I'm a little bit confuse with the use of filters. When can I use filter by default just by draging it to the cava, and when I should creat the filter manually? I've been draging all the filters and now I don't know when it is appropiate to create them manually.. I understand if I want to show rankings I should create the filter as you explain, but in which other option I should do it? Regards!

[@CurbalEN]

+Jormary Jackson Hi Jomary, thanks! What do you mean with create the filter manually? /Ruth

[@tobiascrush5314]

I found this video extremely helpful for something I'm working on. You mention that it is possible to exclude the non displayed values from the matrix totals. I was wondering if you have posted a video on how to do this or can describe how to?

[@devangmistry6307]

Hi Ruth, Excellent video, many thanks for this. One question, what is we don't want to do Avg Value and rather use the Value column from the Table to display top 5 or top 3 Value.

[@CurbalEN]

+Devang Mistry I will have to re-watch the video, But until I do, there are always multiple ways to solve the same thing, so you are probably right! /Ruth

[@matthewappleyard2708]

Interesting video. For some reason the new measures only work on mine if I use the built in format 'TopN' and not without single quotation marks as yours was. Not sure if this is a version quirk?!

[@CurbalEN]

Weird! Can you report it as an issue on issues.powerbi.com? /Ruth

Nice video, great tip, thanks

[@CurbalEN]

You welcome Diego :) /Ruth

[@gergelykosi8199]

Clever solution...like it. Thank you!

[@CurbalEN]

Wondeful to hear :)

[@nagarajpalani861]

Hi Ruth What is difference between hasonevalue and selectedvalue why u didn't use selectedvalue

[@shahrzadkarimi6466]

It realy helped me, Thank you 👌

[@pratikfutane8131]

Many concept covered under a single hood..thanks 👌

[@CurbalEN]

You welcome :) /Ruth

[@pratikfutane8131]

@@CurbalENGracias

[@CurbalEN]

👏 /Ruth

[@osamaasif9601]

Hey Ruth, Please make videos on Power BI paginated reports.

[@sahilsinghal9472]

Fantastic video

[@CurbalEN]

Wow, Thanks :) /Ruth

[@JoseSilva-zn3kk]

Hi Ruth, great example. Thanks. But, what about the totals? when you select top 5 or top 10 the total should be filtered, right? how do you do that? many thanks :)

[@CurbalEN]

+Jose Silva Hi, Have you checked this video: m.kzbin.info/www/bejne/q5ergIKCmc6SoM0 /Ruth

[@JoseSilva-zn3kk]

Thanks Ruth, that has solved my problem!

[@CurbalEN]

+Jose Silva Great!! Really happy it helped 😀 /Ruth

[@jessicabelfield]

Thank you so much, this video is awesome!!

[@daniel.balandra]

Que tal Ruth? Excelente video, me sirvió de mucho para este tipo de reportes. Solo que tuve un percance y aún no entiendo porque no funciona. Cuando uso RanX para los items con la tabla "ALLSELECTED('TABLE')", el filtro funciona bien seleccionando cualquier opción, excepto cuando coloco "Seleccionar Todo", en ese momento la medida solo me muestra del rank Nro 1, y obvia el resto. Ya revise el medida que contiene el numero de seleccion del valor del TOPN, y obtiene el valor adecuado, pero la medida del valor a mostrar en la tabla solo me muestra de 1 solo item. Cuando cambio esta opcion en la medida del Rankind, de "AllSelected" a "All". El problema se soluciona. Me gustaría entender por qué tiene ese comportamiento con "AllSelected" con el fin de entenderlo mejor. Me puede ayudar? Slds cordiales

[@CX004]

Hi Ruth, Thanks so much for your well-explained video! I would like to ask if there is a way to communicate to all other columns in the table while applying the TopN slicer? Eg if TopN10 is chosen, the table will only show first 10 rows across all columns. Kindly assist. Thank you! :)

[@CurbalEN]

Yes, of course , the filter is applied on all columns. /Ruth

[@CX004]

Oh. What I meant is, for example at 8:55 of this video, when slicer TopN5 is selected, only the 2nd column shows up 5 values but the 1st and 3rd columns remain with 10 values each. Is there a way to work dynamically for all 3 columns?

[@CurbalEN]

Ok, I need to check the video, I did it a while ago. Hopefully I have time this evening! /Ruth

[@azizquazi]

Hi Ruth, a nice movie. Need help - if my measure is SUM (instead of X), then the total with the selection Top 3, / Top 5, etc. do not give us the correct value (it gives us the max Total). How to solve to get the correct value. Thanks.

[@MarkCBB86]

Very clever, thank you for sharing

[@CurbalEN]

+Mark Blackburn Thanks Mark! /Ruth

[@mazharmohammed6475]

Hi Ruth,. I got one question regarding TopN Slicer can we also slice Legend values in Visual

[@rodribezul]

Alguna sugerencia para lo siguiente: Tengo una medida %Ejecución, necesito seleccionar los registros que están en un determinado rango . Una especie de KPI sobre este valor %ejecución. Por ejemplo seleccione solo los que dicho porcentaje sea mayor del 70% etc. Gracias.

[@CurbalEN]

+Rodrigo Bedoya Zuluaga Hola Rodrigo, No es fácil contestar a tu pregunta aquí, podrías preguntar en el Power Bi community? Puedes preguntar en español, no es problema :) /Ruth

[@patricksullivan6362]

Hi Ruth, In the filter at the beginning of the video there was an option of "All". How does one achieve an "All" option in addition to the Top 3, 5, and 10? Thanks!

[@CurbalEN]

+Patrick Sullivan Hi! It is a setting in the filter itself. Select the filter, go to the formatting pane and the first or the second setting you will be able to turn that on. Sorry for the vague instructions, I am pulling from memory. /Ruth

[@patricksullivan6362]

Thanks, Ruth!

[@CurbalEN]

My pleasure and have a great weekend :) /Ruth

[@patricksullivan6362]

You too!

[@LashaPower]

Cool, But Could you assist me how to get total Value for TopN values

[@aas2943]

RUTH, ur work is highly appreciated,,,,,,,, kindly; make video for 2021 for simple top 5; bottom 5 and how to connect it with other charts (bar, pie, etc) it would be a great help for the community

[@CurbalEN]

Use the filter pane for that, easiest way!

[@aas2943]

@@CurbalEN yes ; true and how to connect that with entire db for bar charts and pie charts

[@CurbalEN]

That is a question for the ppwer bi community. Give as many details as you can to get help :)

[@aas2943]

@@CurbalEN yes ok....... ur videos r always motivation 4 me

[@CurbalEN]

Thanks!

[@אלונהלוגסי-ש2ח]

Hello, in the total the AVG is AVG for all products and not for the TOP10. please advice how to fix it. thank you, Alona

[@GustavoHeliAliagaGalvez]

Buenos días, una consulta estoy viendo en la última parte que editas la función: SelectedTopNValue, sin embargo no veo que esa medida se use, solo veo que pusiste la medida: AvgMeasurementValue como columna, podrías explicarme ello. Gracias

[@jessicabelfield]

Your videos are so helpful, thank you. Do you have anything on price indexing with dax?

[@CurbalEN]

Hi Jessica, thanks! No, unfortunately nothing. Try in the Power bi Community to get help on your case. /Ruth

[@RehanShaikh-ip9hh]

Hi, when u select topn 3/5, the avg of machine measure is not appearing. Secondly rank total appearing is 6 everytime which is not correct. Pls let us know if there is second video of this rankx

[@sobsawats.9109]

In last part of VDO, when removed "Rank", order is not by "Rank" but "Machine". Am I miss something/understanding?

[@davidvanek3927]

HI, very nice videos, maybe you have a nice and simple solution, how to put in slicer to see last 2 /4 /8 /16 months sales development. thank you David

[@CurbalEN]

+David Vanek Hi David, Thanks! Maybe this video can help you? kzbin.info/www/bejne/nYrFeIGbjqlnhbs /Ruth

[@ShivShakthi.Shanmuga]

Awesome 🙂

[@tejashmonani1792]

I want to use Top 5 & Bottom 5 with QTD, YTD slicer. Can you help me with this query?

[@bluetaurean]

How can you use this for filtering on basis a measure that is not on the view. for example for the top N inventory items based on value, how can i filter to see the sales or order on hand. so i will have the sales data or order data in the view but the list to be filtered for the top N items that i am holding in stock.

[@EastAG]

This works for a table but I am trying to get it to work for a stacked column chart and it does not filter to the top N companies in my data set on the chart.

[@ellukayou]

Beautyfull, i did not understand why when i select ·Top10" it shows me the Total

[@madiakns]

Thanks... It's very helpful :)

[@CurbalEN]

🥳🥳 /Ruth

[@Kohatianayub]

when I select top 3,5,10 I get total of table at bottom , in your case u don't get it , if I need total as well for top 3,5,10 how to get it in table .. thanks

[@CurbalEN]

+Yasir ayub Hi Yasir, Please post your question in the Power BI Community, they will be able to help you! /Ruth

[@pravinjaiswal7219]

Hi Curbal your videos are amazing. could you please suggest me something on my current query. where i want to show past ,present and future data based on month selection ,if user selects Mar-19 ,he should see 3 visual one for past one for present one for future with two columns ,product name and its delivery date. kindly suggest.

[@CurbalEN]

Hi Pravin, Hi! Can you check with the Power bi Community? Here is how and why: m.kzbin.info/www/bejne/pYmpgJiYqtuFsJI /Ruth

[@AlejandraCervantesphysisR]

Hi! Thanks for your videos, are really helpful!! And i want to know if you can post something about the hierarchy slicer. Is not to difficult theme but i haven't found a good explanation. Thank you, again!

[@CurbalEN]

Have you seen this one? m.kzbin.info/www/bejne/eoiYf41-ib-fhtk /Ruth

[@zuhedagulmohamed5726]

How could we modify the measure to say don't show me anything if nothing is selected from TopN? In other words, start with an empty list.

[@krishnakishorepeddisetti4387]

Is there a way to show or remove the total that is showing at the bottom..??

[@kapildhall5205]

Superb, Thank you!

[@CurbalEN]

Awesome! /Ruth

[@tejeshgour1919]

= TOPN ( 3, SectionEnrollment, FILTER ( SectionEnrollment, NOT ( ISBLANK ( SectionEnrollment[SchoolYear] ) ) ), DESC ) I am using given above DAX to show top 3 years data without blank in SSAS TOM model but getting error. Please help me!

[@CurbalEN]

Can you post this in the power Bi Community and provide some sample data? /Ruth

[@adineshkumar8086]

Hi i have small doubt while we are displaying Rank value usine rankX DAX function.Some times we are getting same rank for same values right. For ex: Machine 11 ---- 0.65 --- 8 Machine 14 ---- 0.65 --- 8 Here, Could you please tell me .I want to display the unique rank based on machine numbers.ofcourse values are same but machines are diffrent right.How could we show the different rank? in this point of scenarios

[@mustafabiviji00711]

Hi Curbal, Thank you for this video. I had a question - Suppose the user selects top 3 values is there a way to group(sum/avg) the remaining values of the remaining machines. For example if we are looking at sales of 12 different machines in a pie chart and you just want to see/focus on the top 3 but also want to see the sales of the remaining 9 machines grouped into another category 'Other' and show it on the pie chart as a 4th slice of the pie, is there a way to do that?

[@CurbalEN]

Hi Mustafá, could you post your question in the power bi community? Make sure you post sample data. /Ruth

[@mustafabiviji00711]

Roth, will do so. Have already posted this as an idea as Power BI should add the Top N as a Quick Measure and support the Other categories like ZoomCharts provides. Would appreciate if you and others could support the idea (ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20317618-show-hide-top-x-categories-in-visuals-with-option )

[@mustafabiviji00711]

In addition to that I found this post (community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672) which when combined with Top N table/measure logic you explained would address my requirement. Would have liked this to be out of the box for Power BI or at least as Quick Measure

[@CurbalEN]

Thanks for sharing the link :) /Ruth

[@CurbalEN]

Agree! Maybe soon? /Ruth

[@mathew9665]

Quick question - how you can you get top X N and then the remaining as Others. Ideally we would be able to click into other to then show the next top X n (with any remaining as others ) and so on. is this doable

[@CurbalEN]

I have seen it done, but never tried it myself. Do a quick google search for “topn others power bi” and you will get some suggestions on how to do it. Happy Friday!! /Ruth

[@mathew9665]

Any chance of a video ? :-)

[@CurbalEN]

Sure, no problem :) Is on my list now! /Ruth

[@mathew9665]

:-) !!!!!! thanks Ruth /mathew

[@Nature-iw2ez]

hi, so in the latest version of power bi we can do this automatically by using the filter pane ryt?

[@guilhermemachado963]

Hi, Ruth, thanks for your tutorials I´m learning a lot with it! Does this file is still avaiable for download? I couldn´t find it on Community downloads =(

[@CurbalEN]

dax Fridays files are on the DAX Fridays folder!

[@guilhermemachado963]

Thanks, Ruth!

[@shinky0519]

What if i want to display the top 3 ranks as well

[@subbukk4112]

could you pls share one more video based on multiple visuals using same slicer

[@CurbalEN]

It sounds like you have a specific case in mind, have you tried posting in the power bi community? The solution will depend on how your model looks like and what you are trying to rank. /Ruth

[@subbukk4112]

i have three visuals like productwise sales,custwise sals,state wise sales three different visuals so on three visuals using one slicer when i select top3 in slicer auomatically i want display top3 cust,top3states,,,top3 states on my visuals

[@CurbalEN]

Thanks, please post the question in the power bi community,you will get help in no time :) /Ruth

[@lauricevillarino8713]

Is there a way I can export my topN table with slicer? Whenever I export to excel, it exports the whole rows (sample: 50 rows instead of the top 10) Thank you!!!

[@hamzarafique3857]

Could this work if I added percentages instead of whole numbers in the TOPN table?

[@gpearls6232]

How do we achieve the reverse (Bottom)?

[@MrVuducviet007]

Hi Ruth, I followed you step by step to do TopN like you did, but, was not success. I replaced [Avg Value] by [AvgMeaurementValue] but noticed that can not update the visual coz have somethings wrong in [AvgMeasurementValue], (I checked again and agian to compare with you formula), consider using VALUE or FORMAT Function to fix it. Have you ever got this error like this? and how to fix it?

[@CurbalEN]

+viet vuduc Hi Viet, Did you download the example file from me? Does that work for you? What error are you getting? /Ruth

[@MrVuducviet007]

Hi Ruth, I learned then tried to apply to my BI Report. I'll do it again then record a clip then send you for more detail. Thank you for very usefull Topic "DAX Friday". /Viet

[@CurbalEN]

+viet vuduc You welcome! :) /Ruth

[@ndb2702]

thank you for great video series. I had a question and I would appreciate you could provide answer to it. Is there a way to display only the top n records and not all the records after you apply the filter?

[@CurbalEN]

+Ndb Bumb Hi Ndb, which filter are you referring to? Thanks for watching the series! /Ruth

[@ndb2702]

hi curbal: thank you for the message. I am referring to after we select top 10 on the slicer thr logic filters top 10 for the average measure column but still shows all the machines and the ranks listed. Is there a way to filter the machines and ranks along with the average measure. hope I am able to explain.

[@CurbalEN]

Hi Ndb, I am afraid I still dont understand your question. When you filter by, for example top 3, the list will show you the top3 measurement values and their corresponding machines. What do you mean when you say that it shows all machines? /Ruth

[@RichardOliver]

If I've understood you correctly, then this is what the behaviour was once the rank column was removed from the visual.

[@aperez1969]

Me resulto lograr filtrar por los distintos top. Pero cuando no selecciono nada me devuelve el valor máximo y a ti te devuelve todos los valores. Que hago?

[@lifeisstr4nge]

Rank doesn't work without all, right?

[@tubeitz]

brilliant! thank you

[@CurbalEN]

Thanks!!! /Ruth

[@lawrenceonline]

Thanks for the video bu i keep geting "A single value for column 'Exposure' in table 'Risks' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result" my formula is as follows Measure = RANKX(ALLSELECTED(Risks);Risks[Exposure];;ASC)

[@CurbalEN]

+Lawrence Hi Lawrence! Is Risks[Exposure] a column or a measure? /Ruth

[@lawrenceonline]

Hi Ruth, its a column i think, on risk log its a calculated column value between =Probability (%)* Impact (1-10).

[@CurbalEN]

+Lawrence That is where you have the issue. You can not have columns in measures without a SUM or COUNT or VALUES or something similar. Add one of those as needed and your measure will work! /Ruth

[@lawrenceonline]

thanks Ruth i added SUM(Risks[Exposure])) and it worked

[@CurbalEN]

+Lawrence Excellent! /Ruth

[@simpalranjan4015]

can we do this is power pivot?

[@figarocalisthenics9516]

I have been trying to follow this tutorial but it is kind of hard because I am a newbie to powerbi and finding the menues you click in is hard because I cant see what you click on sometimes since your screen does not show everything. For instance in minute 1:18 you click on something to see the command line, but I cant find what is it you click on to see the command line. Maybe someone can explain. I am trying to make a top ten in a table with contains products, quantities, values in money, and I would like to make top 10 for both top ten number of products, and top ten highest cost. Please get back to me if anyone knows.

[@CurbalEN]

+Figaro Calisthenics Hi Fígaro, yes, I have my Power Bi set up so I have some shortcuts to buttons and also other settings configured like to see the command line. That is not explained in the video so here it goes: to see the command line click on View and then Formula Bar. /Ruth

[@ducthaituyen]

can you show me how to calculate age against one set date, for example I need to calculate a person age from date of birth to 6/1/2015 , and from date of birth to 3/1/2016 etc

[@CurbalEN]

+duc thai Hi Where do you want to do it? Power Query or DAX?

[@ducthaituyen]

Power query in power bi desktop

[@CurbalEN]

+duc thai Doesn't it work by subtracting both dates? /Ruth

[@ducthaituyen]

no for example i have a person date of birth 2/1/2001 i want to add few columns to show his age at 2005-6-31 ,2006-6-31, and 2007-6-31 etc for reporting period

[@CurbalEN]

+duc thai So you can add a custom column and then subtract the dates? I don't think I am understanding your question. It is better if you publish it on the power bi community where you can show your data. /Ruth

[@onlyespresso]

Hi Ruth, How to add All data section in slicer like you ?

[@CurbalEN]

Hi, it is a setting on the formatting panel for the slicer called slicer controls or something like that :) /Ruth

[@simpalranjan5161]

Cab we do this is power pivot if yes than how to do the data connectivity

[@CurbalEN]

Hi, You should be able to follow the same steps. /Ruth

[@simpalranjan5161]

@@CurbalEN Hi Ruth, Top n Slicer is working issue is if i select top5 or top 10 than the it filter out correct data but the grand total remain same always how can i fix this please suggest.

[@CurbalEN]

Have you seen this? kzbin.info/www/bejne/q5ergIKCmc6SoM0&vl=en /Ruth

[@simpalranjan5161]

@@CurbalEN Ruth, I have already seen this video same condition i m using with TOP n slicer but its is not giving correct total. Please help me out.

[@CurbalEN]

For support, please post in the Power BI community. Make sure you explain your case in detail to get help. /Ruth

[@MarkHinderliter]

Hi Ruth, When I try to download the source file for this to shortcut building the machines table, I get this alert: Your connection is not private Attackers might be trying to steal your information from curbal.synology.me (for example, passwords, messages, or credit cards). Learn more NET::ERR_CERT_COMMON_NAME_INVALID Do you know how to resolve this?

[@CurbalEN]

Hi Mark, issues with my SSL certificate . Click advance and then proceed and you will be fine, promise! /Ruth

[@shyamthakur1393]

hi....suppose i have 50 record & there i have used "Rank " to get top 20 records...but rest 30 record count i want as " All Others " .....how to achieve this.....

[@CurbalEN]

Hi! Please check this video on how to get Power BI support: m.kzbin.info/www/bejne/pYmpgJiYqtuFsJI /Ruth

[@shyamthakur1393]

@@CurbalEN I hav seen this video but ...i didnt find solution for my question

[@CurbalEN]

Post your question here: community.powerbi.com to get help. /Ruth

[@ЭлектростальскийКолледж-м5й]

Спс!!! Очень познавательно. 😶

[@CurbalEN]

пожалуйста! /Ruth

[@chiu2003MOBILE]

really unclear explanation on the last part (Maxx....) Can someone help?

[@Tyraelxadrias]

The Video is broken!

[@luispitty6808]

I like your voice haha

[@CurbalEN]

Should start a podcast! /Ruth

[@luispitty6808]

@@CurbalEN yes hahaha,

[@jonnyparsons3217]

Great video thanks Ruth. For those experiencing the problem with the incorrect total, there is a work-around here kzbin.info/www/bejne/fnvHh5mPoMubo6c Hope this helps?

[@CurbalEN]

+Jonny Parsons Hi Jonny, I have a video on that too, but thanks for the resource :) /Ruth