Forum Discussion

cmcgo3's avatar
cmcgo3
Helper II
2 years ago
Solved

Filtering Visual for Consecutive Years

I was given a solution back in February, but looking for additional options to resolve.

 

This is probably an easy fix, but I cannot figure out if I need a calculated column or DAX measure.  I do not get the result I want when applying a filter in visual (clustered bar chart).

I have Player 'A' and Player 'B' that participated in both Year 2022 and 2023.  Players 'C' only participated in 2023, while Player 'D' only particpated in 2022.  

I need a filter that excludes Players 'C' and 'D' because I only want to show players that particpated in consecutive years 2022 and 2023.

 

 

 

 

 

 

 

  filter.pbix 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    cmcgo3 

    pls try this

    Column =
    var _last=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=EARLIER('Table'[year])-1),'Table'[year])
    VAR _next=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=EARLIER('Table'[year])+1),'Table'[year])
    VAR _currentyear=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=year(today())),'Table'[year])
    return if(ISBLANK(_currentyear),BLANK(),if(not(ISBLANK(_last))||not(ISBLANK(_next)),"y"))
     
    pls see the attachment below

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    cmcgo3 

    pls try to create a column

    Column =
    var _last=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=EARLIER('Table'[year])-1),'Table'[year])
    VAR _next=maxx(FILTER('Table','Table'[player]=EARLIER('Table'[player])&&'Table'[year]=EARLIER('Table'[year])+1),'Table'[year])
    return if (not(ISBLANK(_last))||not(ISBLANK(_next)),"y")
     
     
     
    pls see the attachemnt below
    • cmcgo3's avatar
      cmcgo3
      Helper II
      2 years ago

      Thank you Ryan, it works just like it is written.  I need one small edit though, If i want the max year to = 2024 which should return all blanks, how would I adjust this calculation?? Thanks!!

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        2 years ago

        what do you mean by max year to 2024? if has Consecutive Years until 2024, then all be blank?

        could you pls update the sample data and expected output?