Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
Hi @Sashwato ,
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.
Hi @Sashwato ,
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.
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
Proud to be a Super User!
User | Count |
---|---|
77 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |