Forum Discussion
Dynamic TOPN
- 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.
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.
Thanks so much for your help. It worked perfectly.