Forum Discussion

DRunner's avatar
DRunner
Regular Visitor
5 years ago
Solved

Dynamic Top N filter for graph

Hi,   I was looking for a solution to my problem when trying to develop a dynamic TopN filter for my report when I stumbled on this post from a few years ago Dynamic Top N filter for graph .   I ...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, DRunner 

     

    I modify the measure, because column won't change when using slicers. You need to use allexcept funciton.

    Measure = RANKX(ALL('Dataset 1'[CODE]),CALCULATE([sumLBS],ALLEXCEPT('Date','Date'[Year])),,DESC)
    Measure3 = 
IF (
    ISFILTERED ( 'Top N'[TopN] ),
    IF (
        [Measure]
            <= SELECTEDVALUE ( 'Top N'[TopNValues] ),
        1,
        0
    ),
    1
)

     

     

    Best Regards

    Janey Guo

     

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

DRunner's avatar
DRunner
Regular Visitor
5 years ago

Thanks v-janeyg-msft!

 

This is almost working. The only issue I am seeing is when I select different Work centers (for example work center 45) measure 2 is returning a value of 0 for the top code and a value of 1 for the 6th code. This is causing issues with the graph.

 

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

Hi, DRunner 

 

I modify the measure, because column won't change when using slicers. You need to use allexcept funciton.

Measure = RANKX(ALL('Dataset 1'[CODE]),CALCULATE([sumLBS],ALLEXCEPT('Date','Date'[Year])),,DESC)
Measure3 = 
IF (
    ISFILTERED ( 'Top N'[TopN] ),
    IF (
        [Measure]
            <= SELECTEDVALUE ( 'Top N'[TopNValues] ),
        1,
        0
    ),
    1
)

 

 

Best Regards

Janey Guo

 

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

  • DRunner's avatar
    DRunner
    Regular Visitor
    5 years ago

    Thank you for your help!