Forum Discussion

bdog1971's avatar
bdog1971
Regular Visitor
4 years ago
Solved

Filter Ranking Table based off Slicer

I am a beginner to PowerBi and I need to filter my table by the Ranking column to only show the top 5 records, top 10 records, top 15 records, bottom 5 records, bottom 10 record.    Table Below.    ...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    bdog1971  you can use this

    Measure =
VAR _AscendingOrder =
    RANKX ( ALLSELECTED ( t1 ), CALCULATE ( MAX ( t1[Ranking] ) ),, ASC )
VAR _DescendingOrder =
    RANKX ( ALLSELECTED ( t1 ), CALCULATE ( MAX ( t1[Ranking] ) ),, desc )
VAR _selection =
    SWITCH (
        TRUE (),
        CONTAINSSTRING ( SELECTEDVALUE ( 'New-Slicer'[Category] ), "Bottom" ), _AscendingOrder,
        _DescendingOrder
    )
VAR _value =
    IF ( _selection <= SELECTEDVALUE ( 'New-Slicer'[Value] ), _selection )
VAR _select =
    SELECTEDVALUE ( 'New-Slicer'[Category] )
RETURN
    IF ( _select = BLANK (), MAX ( t1[Ranking] ), _value )

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I would say there are several ways to get desired output, but based on the question I'm not sure if you want many tables, visual or other output. Just by loading your table to PBI you can use the ranking in a slicer:

or as a filter in the filter pane:

or to the table it's self as  a "TOP N" using TOP/BOTTOM:

 

 

bdog1971's avatar
bdog1971
Regular Visitor
4 years ago

The top one would be the best for what I am doing.    I was trying not to hard code the values but let the reader of the report be able to select what they wanted.