Forum Discussion
Filtering a table using TOP N slicer
Hi,
i have a TOP N slicer which is a what if parameter from 0 to 50, i am trying to filter TOP N qty for a product in a table.
please note there is already a TOPN filter on week for the table visual like pic below, so another topn is not possible
and the data does not have an id/unique column and the products are repeted several times and the week in the sample is different from the topn loaddateweek.
I am using a DAX measure to calculate the top products
| Week | Product | Still to supply_sum | rank |
| 2024 34 | 6708 | 58720 | 1 |
| 2024 40 | 7706 | 64919 | 1 |
| 2024 38 | 6708 | 48120 | 2 |
| 2024 40 | 6708 | 43040 | 2 |
| 2024 38 | 731 | 34400 | 3 |
| 2024 36 | 6708 | 33560 | 3 |
| 2024 39 | 7448 | 36000 | 3 |
| 2024 41 | 6961 | 32040 | 5 |
| 2024 38 | 300 | 30080 | 6 |
| 2024 36 | 300 | 28120 | 7 |
| 2024 30 | 7551 | 24720 | 9 |
| 2024 41 | 122116010 | 22160 | 10 |
| 2024 36 | 731 | 21560 | 11 |
| 2024 41 | 7551 | 19790 | 15 |
| 2024 32 | 7551 | 19168 | 17 |
| 2024 31 | 300 | 16204 | 24 |
| 2024 32 | 300 | 15669 | 25 |
Not sure how to solve this issue to just have rank that does not repeat for a another row for the same product.
Thanks for the help in advance.
Hi Bu__ ,
Do you mean that you want to show TopN for each week? I think you can try ALLEXCEPT() function.
rank = VAR SelectedTop = SELECTEDVALUE('TOPN'[TOPN]) RETURN SWITCH(TRUE(), SelectedTop = 0, [Still to supply_sum], RANKX( ALLEXCEPT('Table','Table'[Week]), [Still to supply_sum], ,DESC,Dense ) <= SelectedTop, [Still to supply_sum] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Bu__ ,
According to your screenshot, I think you add the [Latest load date] to filter the week column. However I couldn't find the logic about [Latest load date].
Here I still have some questions:
1. Is [Still to supply_sum] a measure or a column?
2. How did you calculate [rank] column? And I couldn't find [TOP N measure] in your statement.
Can you share a sample pbix file with us and show us the result you want? This will make it easier for us to find the solution.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
yes the screenshot shows to filter for the latest week data, here it filters till the latest load date (which will be the max date).
1. yes still to supply is a measure
2. rank is a measure calculated like below
rank=VAR SelectedTop = SELECTEDVALUE('TOPN'[TOPN])RETURNSWITCH(TRUE(),SelectedTop = 0, [Still to supply_sum],RANKX(ALLSELECTED(table[week],table[Product]),[Still to supply_sum], ,DESC,Dense) <= SelectedTop,[Still to supply_sum])i would like to have something like below:when i select top N filter=1 just show 1 product in a week.
Right now the problem is i get top1 product for several weeks when the topn filter=1.
Hi Bu__ ,
Do you mean that you want to show TopN for each week? I think you can try ALLEXCEPT() function.
rank = VAR SelectedTop = SELECTEDVALUE('TOPN'[TOPN]) RETURN SWITCH(TRUE(), SelectedTop = 0, [Still to supply_sum], RANKX( ALLEXCEPT('Table','Table'[Week]), [Still to supply_sum], ,DESC,Dense ) <= SelectedTop, [Still to supply_sum] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
