Filter Ranking Table based off Slicer

[bdog1971]

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

[Ashish_Mathur]

Hi,

See if the link here helps - Vendor Analysis In Inventory Management Dashboards.

[Anonymous]

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]

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.  

[smpa01]

@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

[bdog1971]

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 · Accepted solution]

@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 )

[bdog1971]

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

[smpa01]

@bdog1971  can you please post the pbix?

[bdog1971]

Here you go. 

Test V2.pbix 

[AlexisOlson]

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

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