Forum Discussion
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. ...
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 )
What do I need to add so when nothing is selected in the slicer that is shows me all the records instead of no records? Sorry I shuold have added that in my explanation?
Thank you so much for the help.
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 )
Ok still having one issue but can not seem to find it in your file. Then I select "Top5" is does not remove the other records like yours does. As seen in the below image. Yours takes away all the records. I see there is a Ranking fileter on yours when I mouse over it but not sure how to get that or where to set it. Thanks
Here you go.