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 =
    RANKX (
        all(fTwitchData),
        CALCULATE (
            [Hours_Watched],
            ALLEXCEPT ( fTwitchData, fTwitchData[Game] )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
  IF ( _A < 6, [Game], "Other" )

 

However the TOP 5 list that i created isn't dymamic and not changes when i change the year. It should change. I d like to use a chart per Group filtering per year. Every time i change the year, the TOP 5 list needs to change too.

I put below two chart with different filters. The TOP5 list is the same, but it should not to be.

 

 

PBIX File

https://drive.google.com/file/d/1EbmFs4YofT2VZpx2z9OfbKKVDR-B4gKx/view?usp=sharing 

 

 

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.

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    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

      Thanks so much for your help. It worked perfectly.

    • 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?