Forum Discussion

Icon for Helper I rank

Helper I

3 years ago

Creating 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 re...

Icon for Community Champion rank

Community Champion

3 years ago

SuddenClarity Use ALLEXCEPT instead of ALL, or use this instead: https://youtu.be/fH0VcV9Smow

ALLEXCEPT Without CALCULATE - Microsoft Power BI Community

SuddenClarity
Helper I
3 years ago
Greg_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
  Community Champion
  3 years ago
  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.
  - SuddenClarity
    Helper I
    3 years ago
    Greg_Deckler Thanks, this is kind of driving me nuts here. No matter what I try, I am still returning only the sales of the latest year, or 2022. For e.g. I tried:
    
    Measure = CALCULATE (
    [Sales],
    ALLSELECTED('Date'[Year])
    )
    
    And I still end up with the same output as my screenshot above. Even though I know it should return the sum of [Sales] across all years in the matrix.
    
    Any idea what may be causing this behavior?

Related content