limit TOPN

[marijane]

Hi  everyone,

I want the top ten products by value so I filter the top N 10 on visual  and the table only shows the ten products by value which is right. The problem is when I add columns to the table like below.  The table shows many more rows. I only want the top 10 by product but showing the other columns. Can you help?

[v-lionel-msft · Accepted solution]

Hi @marijane ,

Is this the problem?

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marijane]

Amazing! Thank you so much!

[marijane]

sorry I forgot the file https://drive.google.com/file/d/1VvHWlPjp4XQYhsNcBpF-nGVHVtUiAGiV/view?usp=sharing

[v-lionel-msft]

Hi @marijane ,

How about doing like this?

If it's helpless, please show me your .pbix file or sample data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marijane]

The problem is that there is a repetition in many columns that's why it is adding more rows. I send you a sample.  In this case, I want the Top 2 but there are 3 rows instead of 2 (because of the repetition of one column).

Thank you in advance 🙂

[marijane]

Hi @amitchandak , thank you for your reply. The columns that I have to add are not from the same table, all the columns are from different tables. Can you be more clear please?

[amitchandak]

@marijane , IN case of Rank and TOPN if you take any other column then product, then Rank will be inside that /those columns . So either you should consider then in rank/top n or do not use in visual

example

Geography Rank = RANKX(all(Geography[City]),[Sales])  // with City ID give Rank =1  only
Geography Rank = RANKX(all(Geography),[Sales])  // work with City id as city and City id are both unique
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales]) // work with City id as  as both included

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...