cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ruthie09
Resolver I
Resolver I

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:

  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

Ruthie09_2-1652909721599.png

 

#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.  
 
Ruthie09_4-1652909975415.png

 

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
tamerj1
Community Champion
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)] )
    )
)

 

View solution in original post

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

 

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

View solution in original post

6 REPLIES 6
tamerj1
Community Champion
Community Champion

@Ruthie09 

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. 

@tamerj1 

 

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?

tamerj1
Community Champion
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)] )
    )
)

 

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

 

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

johnt75
Solution Sage
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.

@johnt75 

 

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors