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.

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

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#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 <14)

, cte2 as (select 'Machine ' + cast(b.id as varchar) Machine,
'Operator'+cast(a.id as varchar)'Operator' from cte a
cross join cte b)

, cte3 as (
select *, 20.0/convert(int, 1 + 400 * from cte2)

select * from cte3 where AvgValue < 1

[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.

[VinayKumar-ijeu]

Awesome explanation of this function!! Thanks Ruth!!

[zuhedagulmohamed]

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

[ruchigupta]

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

[prasad]

Thank you for making such interactive example

[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.

[NorbertoVeraReatigaNVR]

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

[ihebchafai]

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

[shashindulakshitha]

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.

[RehanShaikh-iphh]

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

[justfun]

Hi Ruth,

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

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

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

[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.

[Nature-iwez]

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

[ashokkumarbandaru]

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

[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.

[patricksullivan]

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!

[lauricevillarino]

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!!!

[tejeshgour]

=
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!