cancel
Showing results for
Did you mean:
Resolver I

## RemoveFilters is not working

Summary:

I am trying to create a clustered table that contains:

1. Total incidents for the selected month by week (W1, W2, W3 & W4)
2. Total incidents for the previous month by week
3. The average total incidents for all months by week

#1 and #2 are great.  However, #3 just isn't working.  You can see in the image that the line is simply picking up the selected month's values.  Here is the formula I am currently using for #3:

_Inc SC Avg of All 2 =
CALCULATE ( [_Inc SC Avg of All] , REMOVEFILTERS ('DATE Table'[Year Month Name]))

where "_Inc SC Avg of All"
_Inc SC Avg of All = AVERAGEX ( VALUES( 'DATE Table'[_Date SC Week of Month (# only)] ), 'Incident Measures'[_Inc Count] ) / [_Count of Months]

Details:
In a simple table, the Avg of All 2 formula works great...when nothing in the slicer is selected or when the table is circle slashed via Edit Interactions.

But, obviously, I can't use either of those options because #1, 2 & 3 are in one chart and not a separate table.  I simply cannot get Avg of All 2 to work no matter what I've tried.  "ALL" doesn't work; "REMOVEFILTERS" doesn't work.  I've tried complex formulas (thank you @tamerj1 ) and simple formulas like that shown above.

How do I get this to work??????  After scouring the internet trying different things for the last 2.5 days, I am completely out of ideas.  😓

Extra info:

"Avg of All" and "Avg of All 2" are measures stored in a measures table, using data from the main Incidents table.

"Inc Count" is a measure stored in the above mentioned measures table.

"Year Month Name" is a text date column in my DATE Table.

"Count of Months" is a whole number measure in my DATE Table.

"Week" (aka "_Date SC Week of Month (W#)" ) is a text column in my DATE Table.

"_Date SC Week of Month (# only)" is a whole number column in my DATE Table.

This is a link to my previously posted thread that includes more data (including a table of source values) and @tamerj1 's suggested formulas.  Obviously, they didn't work.
2 ACCEPTED SOLUTIONS
Community Champion

@Ruthie09
I may suggest

``````_Inc SC Avg Count by W# =
DIVIDE (
AVERAGEX (
VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
CALCULATE (
[_Inc Count],
ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (Week Name] )
)
),
CALCULATE (
DATEDIFF ( MIN ( INCIDENTS[Date] ), MAX ( INCIDENTS[Date] ), MONTH ),
ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (Week Name)] )
)
)``````

Resolver I

Tamerj1 is so beyond awesome, it's amazing.  Never gave up...stayed with it for DAYS and HOORAY!!!!!  FOUND THE SOLUTION.

THANK YOU @tamerj1 !!!!!!!!

6 REPLIES 6
Community Champion

Previously I have suggested this code

``````_Inc SC Avg Count by W# =
AVERAGEX (
VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
CALCULATE (
[_Inc Count],
ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] )
)
) / [_Count of Months]``````

I think you did not notice my other explanation that the first part is working fine. Actually if you just write it like

``````_Inc SC Avg Count by W# =
AVERAGEX (
VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
CALCULATE (
[_Inc Count],
ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] )
)
) / 6``````

You shall get the correct results

the issue is that multiple columns are involved in the filter context. Some of them are hidden. For example you are using week number in the formulas but using the week name in the visual therfore removing the filter from one column shall not work and you are left with two options. Either to use ALLEXCEPT as a CALCULATE modifier or REMOVEFILTERS ( DateTable ) completely then add the VALUES ( Week name or numbe )

Now we need to concentrate on the other measure that counts the number of months.
You need to notice two things. First is that VALUES ( months column ) is evaluated over the current filter context. Which means if a month is selected it will retun one, if two months are selected then two and so on. The 2nd issue is that the available months in the date table can be more than the number of months with sales or whatever data. Therefore, we can for example

CALCULATE ( DATEDIFF ( MIN (table[Date] ), MAX (table[Date] ), MONTH ), REMOVEFILTERS ( ) )

This should return 6 months as a denominator.

Resolver I

Ok, we are soooooo close.  I changed the Count of Months formula to yours with a minor adjustment (the min date is a hard-coded date of Dec 1, 2021 (the actual date table goes back further) and added 1.  The only thing about the formula is that it returns the same denominator for each week.  This isn't always the case.  For example, right now, W1, W2 and W3 would be divided by 6 but W4 would be divided by 5 since we don't have current data for that yet (there are only 5 data points for W4).  Did that make any sense?

Community Champion

@Ruthie09
I may suggest

``````_Inc SC Avg Count by W# =
DIVIDE (
AVERAGEX (
VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
CALCULATE (
[_Inc Count],
ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (Week Name] )
)
),
CALCULATE (
DATEDIFF ( MIN ( INCIDENTS[Date] ), MAX ( INCIDENTS[Date] ), MONTH ),
ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (Week Name)] )
)
)``````

Resolver I

Tamerj1 is so beyond awesome, it's amazing.  Never gave up...stayed with it for DAYS and HOORAY!!!!!  FOUND THE SOLUTION.

THANK YOU @tamerj1 !!!!!!!!

Solution Sage

Use Performance Analyzer to get the DAX code for the table when a month is selected in the slicer. Have a look at the code in DAX Studio and you may find that other columns are being filtered on, which you will need to use REMOVEFILTERS on in addition to the year / month column.

Particularly with columns involving months its normal to have a different sort by column, so that they're put into the correct order. This column can be added in to the visuals behind the scenes, and so acts as another filter.

Resolver I

I did that with the overall formula but not the count of months formula.  Let me look at that.  It may help me understand @tamerj1 's alternative formula for count of months.

Announcements