Dynamic Top N filter for graph

[DRunner]

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!

[v-janeyg-msft]

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

[DRunner]

Hi!

I uploaded the scrubbed .pbix file to dropbox. Let me know if you can access it.

https://www.dropbox.com/s/5ohj55dwi182x0i/Sample%20Data.pbix?dl=0

Thank you!

[v-janeyg-msft]

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]

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 · Accepted solution]

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]

Thank you for your help!