Using date in a calculation but then removing filters that use the date?

[adamlang]

Hello,

I'm struggling with a requirement in a variable to remove a date filter while at the same time using the date in a calculation which I think brings back the date filter.

VAR _InitialScore =
    CALCULATETABLE (
        SUMMARIZE (
            'Outcome Surveys',
            [Enrollment ID ],
            "FirstInitialScore",
                CALCULATE (
                    MINX (
                        TOPN ( 1, 'Outcome Surveys', [Survey Date], ASC ),
                        'Outcome Surveys'[Gen Well Being Score]
                    ),
                    'Outcome Surveys'[Survey Type] = "Initial"
                )
        ),
        FILTER ( 'Outcome Surveys', [Enrollment ID ] IN _MatchedPairs ),
        REMOVEFILTERS ( 'Calendar' ) 

)

The REMOVEFILTERS seems to have no effect.

The end result for the measure that this varible is part of is a table where each row is a year.  For the other calculations the measure is doing I need to be able to take an Inital Score from a previous year - hence the requirement to remove the date filter. However, dispite the REMOVEFILTERS the table is still filtering this varible by year and so I end up with the wrong result.

Any suggestions or pointers much apprecaited.

Is is part of a wider problem I've been trying to solve for a while, but I think i've narrowed it down to this varible. The full details and wider context can be found here:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Per-Cent-Change-Calculation-in-Surve...

along with the datamodel and PBIX file in OneDrive here (edited):

https://onedrive.live.com/?authkey=%21AIH%2D5hrUiDyzjok&id=321EC98B1E1D23B9%21480&cid=321EC98B1E1D23... 

(Should come up with a message saying that OneDrive can't load a preview but with a download button below)

Thanks.

[adamlang · Accepted solution]

So after about three months trying to get this measure to work, I think I've sorted it.

The variable I was looking for to ensure that the initial survey score was removed from the DATA filter context was: 

VAR _InitialScore2 =
    ADDCOLUMNS (
        SUMMARIZE ( _Initial, 'Outcome Surveys'[Enrollment ID ] ),
        "FirstInitialScore",
            CALCULATE (
                MINX (
                    TOPN ( 1, 'Outcome Surveys', [Survey Date], ASC ),
                    'Outcome Surveys'[Gen Well Being Score]
                ),
                'Outcome Surveys'[Survey Type] = "Initial",
                REMOVEFILTERS ( 'Calendar' )
            )
    )

Hope that's helpful to someone. I've really been struggling with getting the order of nested functions right. Its was about the third read through of the following SQLBI article on SUMMARIZE, that I started to work out what I needed to do.

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/ 

Thanks!

[Anonymous]

Hi @adamlang 

We don't have access to this file.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adamlang]

Thanks @Anonymous,

Does the new link I've added above work?

Thanks,

Adam