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.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

 

 

  • 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.