Forum Discussion
Helper ICreating a measure that ignores a specific filter context
I'm trying to create measure that would ignore a certain filter context but retain all others. In this case, the one I'm trying to ignore is Year.
I have a fact table for my sales, which has a relationship to several dimension tables such as date, product, custom, etc. And now I want to create a measure that would show me the combined sales for all years so I wrote the below measure:
Measure = CALCULATE ( [Sales], ALL ('Date') )
Then to test it out I created a matrix with the years on the rows and put [Sales] and my new measure on the columns but for some reason it's only showing me the sales for the most current year, see below:
I'm not sure what's going on there. Perhaps there's another filter at play that's causing this?
I've also tried CALCULATE ( [Sales], ALL('Date'[Year]) ) but no luck.
6 Replies
- Greg_Deckler
Community Champion
SuddenClarity Use ALLEXCEPT instead of ALL, or use this instead: https://youtu.be/fH0VcV9Smow
SuddenClarityGreg_Deckler Thanks Greg, I'll check out those resources. Do you happen to know why my formula with the ALL function doesn't work?
- Greg_Deckler
SuddenClarity Because ALL strips out all context, including the context that you want to keep. Hence, ALLEXCEPT, strip out all context except the ones that you want to keep. Or, you could use ALLSELECTED, that would keep all filters external to the visual and since Year is in the visual, that would go away. Or, or, you could potentially use REMOVEFILTERS to remove the Year filter.
