Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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: ...
all
REMOVEFILTERS
  • tamerj1's avatar
    tamerj1
    4 years ago

    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)] )
    )
)

     

  • Anonymous's avatar
    Anonymous
    4 years ago

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

     

    THANK YOU tamerj1 !!!!!!!!

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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)] )
    )
) / 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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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)] )
    )
)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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

         

        THANK YOU tamerj1 !!!!!!!!