Forum Discussion
Not working: Top N functionality (TOP N by "count" column if the count is repetitive)
Hii,
As I shown in the above image, TOP N is not working.
Explaination : There are 2 columns in my visual. 1. Pin name and 2. Count of pins. I want top 5 pin's by count of pins. here I applied filter on TOP N by count of pins but its not working. Its only giving me pins having 11 as a count which is max count. 11 count is repetative in this visual. There are other counts as well like 2, 4, 7 and 9 which are repetative but not showing rows with those counts in visual. Please help me in resolving this.
Thanks in advance!
Hi, Anonymous ;
As far as I know, topn is to display the first few, first if you set topn5, then count=11 is the largest sort, but count=11 name is more than 5, then topn5 only display count=11;
For example, if you have four rows count=11 and two rows count=10, then topn5 displays six rows, Anyway, it's counting repeating columns.
so we could create a mesure.
rank = RANKX(ALLSELECTED('Table'),CALCULATE( COUNT('Table'[name])),,DESC,Dense)Then could set less than or equal to .
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
v-yalanwu-msftCommunity Support
Hi, Anonymous ;
As far as I know, topn is to display the first few, first if you set topn5, then count=11 is the largest sort, but count=11 name is more than 5, then topn5 only display count=11;
For example, if you have four rows count=11 and two rows count=10, then topn5 displays six rows, Anyway, it's counting repeating columns.
so we could create a mesure.
rank = RANKX(ALLSELECTED('Table'),CALCULATE( COUNT('Table'[name])),,DESC,Dense)Then could set less than or equal to .
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.v-yalanwu-msft Thank you!
- amitchandak
Super User
Anonymous , based on what I got. Create a dense rank and use that in the filter in visual level filter for <= 5
rankx(allselected(Table[Pin name]), [Count of pin measure],,desc, dense )
Hi amitchandak ,
Thank you for your valuable solution but getting one problem if I select top 3 then only those rows are coming having count 2 and 4 . after 4 it should display rows with 7 count but its not showing so its not working for top 3 , 4 and 5. please help me in this.I attached 2 screenshots after applying top 3 in filter:
1. Ascending of count
2. Descending of count
Written Measure2 as you mentionedThanks!!- amitchandak
Anonymous , can you share the measure. Also make sure in rank you have used measure, not just count(Table[Column]) , if you have used that then use calculate(count(Table[Column]))
