topic Re: Filtering a table using TOP N slicer in DAX Commands and Tips

Filtering a table using TOP N slicer

Bu__ — Thu, 10 Oct 2024 10:26:20 GMT

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

TOP N measure =

VAR SelectedTop = SELECTEDVALUE('TOPN'[TOPN])

RETURN

SWITCH(TRUE(),

SelectedTop = 0, [Still to supply_sum],

RANKX(

ALLSELECTED(table[week],table[Product]),

[Still to supply_sum], ,DESC,Dense

) <= SelectedTop,

[Still to supply_sum]

)

but this is not filtering for only 1 value when top n filter selected =1, but shows multiple value.

Sample data is like below:

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.

Re: Filtering a table using TOP N slicer

Anonymous — Fri, 11 Oct 2024 03:04:31 GMT

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 Zhou

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

Re: Filtering a table using TOP N slicer

Bu__ — Tue, 15 Oct 2024 04:48:03 GMT

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])

RETURN

SWITCH(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.

Re: Filtering a table using TOP N slicer

Anonymous — Tue, 29 Oct 2024 07:14:57 GMT

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 Zhou

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