TopN Dynamic Measure

[emmawdmi22]

I have a report that lists room numbers and how many times they were used in a time period.

I need help because I want to make a slicer that I can choose the Top N highest Times Used values to show (but in order of room numbers, but I don't think that will be hard). I've done some research into Top N tables, but I haven't figured out how to make the N Value work dynamically with a slicer. To be clear, if the slicer says Top 2, I want a table just with the values from Rooms 3 and 9. All of my attempts have resulted in "Top 6" just showing the value from Room 6.

Thanks!

[tamerj1 · Accepted solution]

Hi @emmawdmi22 

You need to have a separate disconnected table that contains the topn numbers to be used as a slices selection. 
Create the following measure, place it in the filter pane of the table visual and select "Is not blank" then apply the

Filter Measure =
VAR N =
    SELECTEDVALUE ( Selection[Number] )
VAR CurrentRoom =
    VALUES ( 'Table'[Room] )
VAR SelectedRooms =
    ALLSELECTED ( 'Table'[Room] )
VAR T1 =
    ADDCOLUMNS ( SelectedRooms, "@TimesUsed", [Times Used] )
VAR T2 =
    TOPN ( N, T1, [@TimesUsed] )
VAR T3 =
    SELECTCOLUMNS ( T2, "@Room", [Room] )
RETURN
    COUNTROWS ( INTERSECT ( CurrentRoom, T3 ) )

[tamerj1 · Accepted solution]

Hi @emmawdmi22 

You need to have a separate disconnected table that contains the topn numbers to be used as a slices selection. 
Create the following measure, place it in the filter pane of the table visual and select "Is not blank" then apply the

Filter Measure =
VAR N =
    SELECTEDVALUE ( Selection[Number] )
VAR CurrentRoom =
    VALUES ( 'Table'[Room] )
VAR SelectedRooms =
    ALLSELECTED ( 'Table'[Room] )
VAR T1 =
    ADDCOLUMNS ( SelectedRooms, "@TimesUsed", [Times Used] )
VAR T2 =
    TOPN ( N, T1, [@TimesUsed] )
VAR T3 =
    SELECTCOLUMNS ( T2, "@Room", [Room] )
RETURN
    COUNTROWS ( INTERSECT ( CurrentRoom, T3 ) )

[emmawdmi22]

Thank you @tamerj1 ! One more quick question that I forgot about. The original table has a date column. Is there an easy way to add this to your filter measure? When I select more than one date with it, all of the data shows instead of just the limited ones.

[tamerj1 · Accepted solution]

@emmawdmi22 
Please try

Filter Measure =
VAR N =
    SELECTEDVALUE ( Selection[Number] )
VAR CurrentRoom =
    VALUES ( 'Table'[Room] )
VAR SelectedRooms =
    CALCULATETABLE ( VALUES ( 'Table'[Room] ), ALLSELECTED () )
VAR T1 =
    ADDCOLUMNS ( SelectedRooms, "@TimesUsed", [Times Used] )
VAR T2 =
    TOPN ( N, T1, [@TimesUsed] )
VAR T3 =
    SELECTCOLUMNS ( T2, "@Room", [Room] )
RETURN
    COUNTROWS ( INTERSECT ( CurrentRoom, T3 ) )