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 am having the same issue where my TopN filter is working great for tables, but it is creating a discontinuous graph when applied there. The filter is grabbing the Top N by month rather than for the entire date range on the report. It seems like the user was able to figure it out, but there really isn't a clear solution posted.  

 

 

Any help is greatly appreciated. 

 

Thanks!

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.

6 Replies

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

    Hi, DRunner 

     

    Can you share some sample data in the form of table and your desired result? I can try to provide you with other solutions.

     

    Best Regards

    Janey Guo

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