Forum Discussion
RemoveFilters is not working
Hi. Yet another post about removefilters not working but none of the other answers have helped. Please, please help me resolve.
Summary:
I am trying to create a clustered table that contains:
- Total incidents for the selected month by week (W1, W2, W3 & W4)
- Total incidents for the previous month by week
- 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:
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.
Anonymous
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)] ) ) )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
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.
Anonymous
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)] ) ) ) / 6You 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 exampleCALCULATE ( DATEDIFF ( MIN (table[Date] ), MAX (table[Date] ), MONTH ), REMOVEFILTERS ( ) )
This should return 6 months as a denominator.
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?
Anonymous
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)] ) ) )
