Remove Report level Year filter on a single visual

[cmncp]

I have a Power BI report that has a Report Level filter to only include data for the last 3 years.   I need to remove this filter for a single visual which needs to show a column chart showing all years, not just the last 3.  I know I need to do this in DAX, but can't work out the formula.  I have tried:

CALCULATE(sum(Sales[SalesAmount]), all('Calendar'[Year]))

But when I use this, my chart still only has the last 3 years on it, but each 3 columns displays the same amount which is the total sales for all all years.

Any suggestions greatly appreciated.

[Greg_Deckler]

This came up before. It was determined that the page filter was pre-filtering things such that DAX could not "bring back" the data by removing the filters.

[cmncp]

Hi @Greg_Deckler   So there is no way to write a DAX measure that overrides the report level filter?

[Greg_Deckler · Accepted solution]

@cmncp - Correct. It's a bit unexpected honestly because one might think that the report and page level filters would just add filter context the way visuals on a page add filter context and thus you would be able to override that filter context within DAX. But, that is not what occurs. The report and page filters pre-filter the data that is even available to DAX when performing calculations. Efficient that way but also a bit unfortunate in circumstances such as this.

[Anonymous]

DAX can override ANY filter and in your case, it demonstrates it clearly.

However, what you are seeing has nothing to do with DAX. Your measure removes all filters from the Calendar, so you see the sum across all the years. But you see only 3 years on the x-axis because the report-level filter puts this constraint on the Calendar and your visual queries the dimension to see which years should be shown. This has nothing to do with DAX. Your DAX removes the filter FOR THE DURATION OF THE CALCULATION correctly. Nothing more.

Best
D