Forum Discussion

captainlaw's avatar
captainlaw
Microsoft Employee
9 years ago
Solved

Top N with Parameter?

Hello Community,
I’m curious if there's a workaround to pass a parameter for Top N filtering? I haven’t seen a good example, so wondering if you know some resources on this.  Customer would like to select N number of legends to be displayed in a chart.

Thanks.

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    captainlaw

     

    You would only need to add additional ALLSELECTED parameters to CALCULATETABLE if you are putting those additional columns on axes (or rows/columns) of the visual itself. If you are simply adding more slicers, then the existing measure would be fine.

     

    However, a better way of defining the measure to capture all possibilities would be:

     

    MyTopN3 = 
CALCULATE (
    SUM ( LAWCrime[Violent crime total] ),
    KEEPFILTERS (
        CALCULATETABLE (
            TOPN (
                [SelectedN],
                VALUES ( LAWCrime[State] ),
                CALCULATE ( SUM ( LAWCrime[Violent crime total] ) )
            ),
            ALLSELECTED ()
        )
    )
)

    This uses ALLSELECTED () without arguments to ensure that the ranking is carried at the total level of the visual (this also means you can change ALLSELECTED ( LAWCrime[State] ) to VALUES ( LAWCrime[State] ).

    If you want to rank by something other than state then just change the red part.

     

    Cheers,

    Owen :)

12 Replies