Forum Discussion

Bu__'s avatar
Bu__
Frequent Visitor
1 year ago
Solved

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 visua...
@
@Dax
@Dax help
@need help
@Please Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

Bu__'s avatar
Bu__
Frequent Visitor
1 year ago

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.

 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.