Forum Discussion

emmawdmi22's avatar
emmawdmi22
Helper I
3 years ago
Solved

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...
Need Help
  • tamerj1's avatar
    tamerj1
    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 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's avatar
    tamerj1
    3 years ago

    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 ) )
tamerj1's avatar
tamerj1
Community Champion
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 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's avatar
    emmawdmi22
    Helper I
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

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