Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Why doesn't REMOVEFILTERS work?

I have a simple formula that calculates the incident count per month, which is then broken down by our defined week of the month (all months have W1, W2, W3, & W4).  Works great.

 

Avg Count per month =
'Incident Measures'[_Inc Count] / [_Count of Months]
 
The problem is I need this to calculate the counts while ignoring the date slicer on the page.  I've tried using REMOVEFILTERS and ALL and nothing has worked:
Avg Count per month - All months =
CALCULATE('Incident Measures'[_Inc Count] / [_Count of Months], REMOVEFILTERS('DATE Table'[Year Month Name]))
 
where "Year Month Name" is the column from the related date table that is the date slicer.
 
I have been fighting this for 3 days and am no closer to a solution.  Note: I cannot use Edit Interactions because the data is actually going into a cluster chart with other data that does need to be affected by the slicer.
Reports

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi Anonymous ,

    Try using ALL in the following manner:

    CALCULATE('Incident Measures'[_Inc Count] / [_Count of Months], ALL('DATE Table'))


    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    If you have a date table, then probably your Year Month Name column is sorted by another column, right? If that's the case, you have to remove the filter not only from the field itself but also from the field by which the field is sorted. To see what happens in your code you can use the Analyzer built in to Power Query. Extract the query and see what happens in there. I'm relatively sure it's to do with the sorting field.

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    Can you try:

    AVERAGEX(ALL('DATE Table'[Year Month Name])),[Inc Count])

    I hope this helps!