all() AND removefilters() not removing slicer filter

[kky1]

Here is a simplified piece within my dax formula using ALL() and REMOVEFILTERS() where I am trying to set a __maxyr variable for the max year in my table Graduation and then a __previousyr variable which is __maxyr - 4. These variables are to do a change comparison over a 5 yr window.

There is a slicer on the page for Year based on a related time table that has a one: many relationship to my Graduation table. 

Troubleshooting, I created test measures to print the year expected in a card visual but measure is not working as expected. It is returning the __maxyr as the year selected in the slicer and __previousyr as 5 years earlier.

-- test 1

6test yr = VAR __selection = SELECTEDVALUE('parameterInstitution'[DisplayName])

RETURN

IF (

    __selection = 'parameterInstitution'[SlicerSelection] && __selection IN VALUES('KPI_Institutions'[DisplayName]), CALCULATE(

            MAX(Graduation[YearInteger]),

            REMOVEFILTERS(Graduation[YearInteger])

        ))

 -- test2

6test yr = VAR __selection = SELECTEDVALUE('parameterInstitution'[DisplayName])

RETURN

IF (

    __selection = 'parameterInstitution'[SlicerSelection] && __selection IN VALUES('KPI_Institutions'[DisplayName]), CALCULATE(

            MAX(Graduation[YearInteger]),

            ALL(Graduation[YearInteger])

        ))

-- sample data from table currently with 2019 as the year selected in the slicer

The Max(Year) in my table is 2021, so the measure shoudl always be comparing rates from 2021 and 2017; however, it is not behaving that way. When I choose 2019, the __maxyr becomes 2019 and __previousyr = 2014. This is causing all the calculations to revert to Blank or zero values. Any advice would be appreciated. The full dax is below. 

Change 4yr grad (5yrs shown) = //-4 shows change in 5 years displayed

VAR __maxyr =

    CALCULATE(

        MAX(Graduation[YearInteger]),

        ALL(Graduation[YearInteger])

    )

VAR __selectedvalue = //Median for max year

    CALCULATE(

        SUM(Graduation[4-year graduation rate p]),

        FILTER(

            ALL(Graduation),

            Graduation[YearInteger] = __maxyr && Graduation[4-year graduation rate p] <> 0

        )

    )

VAR __previousvalue = //Median for n year prior

    CALCULATE(

        SUM(Graduation[4-year graduation rate p]),

        FILTER(

            ALL(Graduation),

            Graduation[YearInteger] = __maxyr - 4 && Graduation[4-year graduation rate p] <> 0

        )

    )

RETURN

    IF(

        ISBLANK(__selectedvalue) || ISBLANK(__previousvalue),

        0.00,

        DIVIDE(

            __selectedvalue - __previousvalue,

            __previousvalue

        )

    )

[kky1]

Thanki you @amitchandak  and @Daniel29195 ! I created a workaround of a new "Year" table based on the max(Year) value minus 5 in a blank query and that worked. But then I tried your suggestion @Daniel29195 and that seemed to work just as well and much more efficient so I will remember that next time - thank you!!!

[Daniel29195 · Accepted solution]

Hello @kky1

did you try removing tthe filter on the whole table instead of on one column ? 

all(table_name) .   or removefilters(table_name)

let me know if it works for you .

[amitchandak]

@kky1 , try with a small change. Also in such cases always have separate year/date table joined with your table and use that in the measure

Change 4yr grad (5yrs shown) = //-4 shows change in 5 years displayed
VAR __maxyr =
CALCULATE(
MAX(Graduation[YearInteger]),
ALL(Graduation)
)

VAR __selectedvalue = //Median for max year
CALCULATE(
SUM(Graduation[4-year graduation rate p]),
FILTER(
ALL(Graduation),
Graduation[YearInteger] = __maxyr && Graduation[4-year graduation rate p] <> 0
)
)

VAR __previousvalue = //Median for n year prior
CALCULATE(
SUM(Graduation[4-year graduation rate p]),
FILTER(
ALL(Graduation),
Graduation[YearInteger] = __maxyr - 4 && Graduation[4-year graduation rate p] <> 0
)
)

RETURN
IF(
ISBLANK(__selectedvalue) || ISBLANK(__previousvalue),
0.00,
DIVIDE(
__selectedvalue - __previousvalue,
__previousvalue
)
)

Example with a separate connected date/year table

This Year =

var _max = maxx(all(Date), Date[Year])

return

CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=_max ))

4th Last Year =

var _max = maxx(all(Date), Date[Year])

return

CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=_max -4))