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.

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

Hi, DRunner 

 

The reason for the problem may be that the rank is the measure, which will change according to the context (month) in the line chart, causing the problem.

You can create a calculated column to rank and create a measure in filter pane to filter top n.

Like this:

Column =
RANKX (
    'Dataset 1',
    SUMX ( FILTER ( 'Dataset 1', [CODE] = EARLIER ( 'Dataset 1'[CODE] ) ), [LBS] ),
    ,
    ,
    DENSE
)

Measure2 =
IF (
    ISFILTERED ( 'Top N'[TopN] ),
    IF (
        MAXX (
            FILTER ( 'Dataset 1', [CODE] = SELECTEDVALUE ( 'Dataset 1'[CODE] ) ),
            [Column]
        )
            <= 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!