Forum Discussion
TopN Dynamic Measure
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!
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 theFilter 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
Please tryFilter 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 ) )
3 Replies
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 theFilter 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 ) )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.
emmawdmi22
Please tryFilter 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 ) )
