Forum Discussion
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.
Thanks
| Ranking | Project | Type | Risk | Threats |
| 1 | Project 1 | Governance | Significant | Major |
| 2 | Project 2 | Project | Significant | Major |
| 3 | Project 3 | Governance | Significant | Major |
| 4 | Project 4 | Project | Significant | Major |
| 5 | Project 5 | Project | Major | Major |
| 6 | Project 6 | Governance | Significant | Major |
| 7 | Project 7 | Project | Significant | Major |
| 8 | Project 8 | Project | Major | Major |
| 9 | Project 9 | Project | Moderate | Major |
| 10 | Project 10 | Project | Major | Major |
| 11 | Project 11 | Project | Major | Major |
| 12 | Project 12 | Project | Significant | Major |
| 13 | Project 13 | Project | Significant | Major |
| 14 | Project 14 | Project | Significant | Major |
| 15 | Project 15 | Consulting | Major | Major |
| 16 | Project 16 | Project | Major | Major |
| 17 | Project 17 | Project | Major | Major |
| 18 | Project 18 | Governance | Major | Major |
| 19 | Project 19 | Consulting | Major | Major |
| 20 | Project 20 | Project | Major | Major |
| 21 | Project 21 | Project | Unspecified | Major |
| 22 | Project 22 | Project | Moderate | Moderate |
| 23 | Project 23 | Project | Unspecified | Major |
| 24 | Project 24 | Project | Major | Major |
| 25 | Project 25 | Consulting | Moderate | Moderate |
| 26 | Project 26 | Consulting | Minor | Moderate |
| 27 | Project 27 | Project | Moderate | Major |
| 28 | Project 28 | Consulting | Moderate | Moderate |
| 29 | Project 29 | Consulting | Unspecified | Major |
| 30 | Project 30 | Governance | Unspecified | Major |
| 31 | Project 31 | Consulting | Unspecified | Major |
| 32 | Project 32 | Consulting | Major | Major |
| 33 | Project 33 | Project | Unspecified | Major |
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 )
10 Replies
smpa01Community Champion
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 _valuepbix is attached
- AlexisOlson
Super User
Looks good. I'd suggest not hard-coding the specific category values though.
VAR _selection = IF ( CONTAINSSTRING ( SELECTEDVALUE ( 'New-Slicer'[Category] ), "Bottom" ), _AscendingOrder, _DescendingOrder ) 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.
- smpa01
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 )
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:
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.
- Ashish_Mathur
Hi,
See if the link here helps - Vendor Analysis In Inventory Management Dashboards.
