Forum Discussion

rodrigosrm2's avatar
rodrigosrm2
Frequent Visitor
4 years ago
Solved

Dynamic TOPN

Hi. I have a column with game names. I created a group to filter the TOP5, using a calculated column like below. I m using the measure Houres Watched to achieve the TOP5. Group = VAR _A = RAN...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi rodrigosrm2 

     

    Calculated tables and calculated columns are not able to be updated by slicers/filters in the report. So your Group column is not dynamic. 

     

    I create a new table in your model, which summarizes on Year and Game at the same time. 

    New Table = SUMMARIZE(fTwitchData,fTwitchData[Year],fTwitchData[Game],"Hours_watched",SUM(fTwitchData[Hours_watched]))

     

    Then add Rank and Group columns in the new table. It ranks games within every year. 

    Rank = RANKX(FILTER('New Table','New Table'[Year]=EARLIER('New Table'[Year])),'New Table'[Hours_watched],,DESC,Dense)
    Group = IF ( 'New Table'[Rank] < 6, [Game], "Other" )

     

    Link Calendar table to this new table on Year columns (many-to-many, single filter direction: calendar table filters New Table).

     

    Then use Group and Hours_watched columns from this new table into the chart.

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

rodrigosrm2's avatar
rodrigosrm2
Frequent Visitor
4 years ago

Hello. Thanks for answering. I've tried your suggestions, however it did not work. When i use the year filter, from Date Calendar, the game list dont change. 

Could you do in my model (the pbix link is above), if is possible?