Forum Discussion
Top N selection dropdown for report filter
Hello all,
I am trying to achieve a report wherein I can filter the Top 5, Top 10, Top 15 based selection from a dropdown or slicer to show the top results of Zip code data based on number of employees. Below screenshot can help understand:
If you see above I need something similar like a dropdown or button can also help in this case - Like Top 5 Zip, Top 10 Zips, etc. I would need the selection to basically filter ZIP codes on the entire report.
Hi Anonymous ,
Based on your description, I did a test. You can do some similar steps as follows.
1. Create a "Top N" table. (I use the "Enter Data" to create it.)
2. create a measure and drag it into the filter of the table visual.
Measure =
var x1=RANKX(ALL('Table'),CALCULATE(MAX('Table'[Value])),,DESC)
var x2=SELECTEDVALUE('Top N'[rank])
return
IF(x1<=x2,1,0)Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous
You Need to Create Rank Function and Filter the Data based on Rank
You have a Slicer already which is showing 5,10,15 (Zip Codes) and let us assume the Column name of those 5,10,15 etc is [N]
Now use this code
VAR Rank of Zip = RankX(all(Table[Zip]), your measure )
RETURN
calculate(yourmeasure,filter(all(table[zip]),rank of zip <= [N]),distinct(zip))This will give you the required information
Hi Anonymous ,
Based on your description, I did a test. You can do some similar steps as follows.
1. Create a "Top N" table. (I use the "Enter Data" to create it.)
2. create a measure and drag it into the filter of the table visual.
Measure =
var x1=RANKX(ALL('Table'),CALCULATE(MAX('Table'[Value])),,DESC)
var x2=SELECTEDVALUE('Top N'[rank])
return
IF(x1<=x2,1,0)Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Could you share some data to work with. Also, please show the expected result there.
