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 )

     

     

smpa01's avatar
smpa01
Community Champion
4 years ago

bdog1971  you can use a measure like 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 (),
        SELECTEDVALUE ( 'New-Slicer'[Category] ) = "Bottom10"
            || SELECTEDVALUE ( 'New-Slicer'[Category] ) = "Bottom20"
            || SELECTEDVALUE ( 'New-Slicer'[Category] ) = "Bottom5", _AscendingOrder,
        _DescendingOrder
    )
VAR _value =
    IF ( _selection <= SELECTEDVALUE ( 'New-Slicer'[Value] ), _selection )
RETURN
    _value

pbix is attached

 

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Looks good. I'd suggest not hard-coding the specific category values though.

VAR _selection =
    IF (
        CONTAINSSTRING ( SELECTEDVALUE ( 'New-Slicer'[Category] ), "Bottom" ),
        _AscendingOrder,
        _DescendingOrder
    )