Creating a measure with a filter that ignores other filters on the visual?

[Vizard]

The visual is filtered to only show the current season and the previous season, but I want to add a line showing the average of the previous five seasons. We have a flag variable in the table indicating the past five seasons with values of either "yes" or "no". 

When I use the following DAX measure, only the past season is brought in for calculation due to the filter on the visual hiding previous seasons:

AVG_FLU = CALCULATE(AVERAGE(Table[count]), FILTER(Table, Table[season_avg5]="yes"))

I have tried using the ALL() and REMOVEFILTERS() functionss but the visual filter still applies no matter what I try. Is it possible to include a filter for calculation while also ignoring other filters on the visual?

[Shravan133]

try this:

AVG_FLU =
CALCULATE(
AVERAGE(Table[count]),
FILTER(
ALL(Table[season]), // Ignore any filter on the "season" column
Table[season_avg5] = "yes" 
)
)

[Vizard]

I get an error saying "A single value for column 'season_avg5' in table 'FLU' cannot be determined"

[Shravan133 · Accepted solution]

AVG_FLU =
CALCULATE(
AVERAGE(Table[count]),
FILTER(
ALL(Table), // Removes all filters from the table
Table[season_avg5] = "yes" // Apply the filter for past 5 seasons
)
)

or

AVG_FLU =
CALCULATE(
AVERAGE(Table[count]),
REMOVEFILTERS(Table[season]), // Removes the visual filter on the season
KEEPFILTERS(Table[season_avg5] = "yes") // Retains only the filter for the past five seasons
)

[Vizard]

The first option resulted in a flat line but the second solution worked. Thank you!