Forum Discussion
emmawdmi22
3 years agoHelper I
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...
- 3 years ago
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 ) ) - 3 years ago
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 ) )
tamerj1
3 years agoCommunity Champion
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 ) )