Filter data between two dates but not another

[Anonymous]

Hello everyone,

I am trying to summarise a data table using the following DAX;

VAR _table = CALCULATETABLE(

SUMMARIZE('GPS Data','GPS Data'[Name],'GPS Data'[Session ID],'GPS Data'[Maximum Velocity (m/s)]),

'GPS Data'[Date] > DATE(2017,07,03),

'GPS Data'[Matchday (+/-)] <> "MD",

'GPS Data'[Matchday (+/-)] <> "PSF",

'GPS Data'[Matchday (+/-)] <> "Off Season",

'GPS Data'[Matchday (+/-)] <> BLANK())

However I now want to apply the filters above to this date range (03/07/2018 - 16/08/2020) but want to include "MD" in the data from 17/08/2020 onwards.

I hope this is clear enough and I appreciate any help.

Thanks

Sean

[AlB · Accepted solution]

Hi @Anonymous 

Try this

VAR _table =
CALCULATETABLE (
    SUMMARIZE (
        'GPS Data',
        'GPS Data'[Name],
        'GPS Data'[Session ID],
        'GPS Data'[Maximum Velocity (m/s)]
    ),
    VAR date1_ =
        DATE ( 2017, 07, 03 )
    VAR date2_ =
        DATE ( 2018, 08, 16 )
    RETURN
        UNION (
            FILTER (
                ALL ( 'GPS Data'[Date], 'GPS Data'[Matchday (+/-)] ),
                'GPS Data'[Date] >= date1_
                    && 'GPS Data'[Date] <= date2_
                    && NOT 'GPS Data'[Matchday (+/-)] IN { "MD", "PSF", "Off Season", BLANK () }
            ),
            FILTER (
                ALL ( 'GPS Data'[Date], 'GPS Data'[Matchday (+/-)] ),
                'GPS Data'[Date] > date2_
                    && NOT 'GPS Data'[Matchday (+/-)] IN { "PSF", "Off Season", BLANK () }
            )
        )
)

or this

VAR _table =
CALCULATETABLE (
    SUMMARIZE (
        'GPS Data',
        'GPS Data'[Name],
        'GPS Data'[Session ID],
        'GPS Data'[Maximum Velocity (m/s)]
    ),
    VAR date1_ =
        DATE ( 2017, 07, 03 )
    VAR date2_ =
        DATE ( 2018, 08, 16 )
    RETURN
        FILTER (
            ALL ( 'GPS Data'[Date], 'GPS Data'[Matchday (+/-)] ),
            IF (
                'GPS Data'[Date] >= date1_
                    && 'GPS Data'[Date] <= date2_,
                NOT 'GPS Data'[Matchday (+/-)] IN { "MD", "PSF", "Off Season", BLANK () },
                NOT 'GPS Data'[Matchday (+/-)] IN { "PSF", "Off Season", BLANK () }
            )
        )
)

Both approaches filter out everything before 03/07/2018

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @Anonymous 

Try this

VAR _table =
CALCULATETABLE (
    SUMMARIZE (
        'GPS Data',
        'GPS Data'[Name],
        'GPS Data'[Session ID],
        'GPS Data'[Maximum Velocity (m/s)]
    ),
    VAR date1_ =
        DATE ( 2017, 07, 03 )
    VAR date2_ =
        DATE ( 2018, 08, 16 )
    RETURN
        UNION (
            FILTER (
                ALL ( 'GPS Data'[Date], 'GPS Data'[Matchday (+/-)] ),
                'GPS Data'[Date] >= date1_
                    && 'GPS Data'[Date] <= date2_
                    && NOT 'GPS Data'[Matchday (+/-)] IN { "MD", "PSF", "Off Season", BLANK () }
            ),
            FILTER (
                ALL ( 'GPS Data'[Date], 'GPS Data'[Matchday (+/-)] ),
                'GPS Data'[Date] > date2_
                    && NOT 'GPS Data'[Matchday (+/-)] IN { "PSF", "Off Season", BLANK () }
            )
        )
)

or this

VAR _table =
CALCULATETABLE (
    SUMMARIZE (
        'GPS Data',
        'GPS Data'[Name],
        'GPS Data'[Session ID],
        'GPS Data'[Maximum Velocity (m/s)]
    ),
    VAR date1_ =
        DATE ( 2017, 07, 03 )
    VAR date2_ =
        DATE ( 2018, 08, 16 )
    RETURN
        FILTER (
            ALL ( 'GPS Data'[Date], 'GPS Data'[Matchday (+/-)] ),
            IF (
                'GPS Data'[Date] >= date1_
                    && 'GPS Data'[Date] <= date2_,
                NOT 'GPS Data'[Matchday (+/-)] IN { "MD", "PSF", "Off Season", BLANK () },
                NOT 'GPS Data'[Matchday (+/-)] IN { "PSF", "Off Season", BLANK () }
            )
        )
)

Both approaches filter out everything before 03/07/2018

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB , thanks very much for your solution which worked for me! Kudos!

From the DAX you have provided me I have done the following to attain the value I need from filtered table;

RETURN

AVERAGEX(TOPN(5,_table,'GPS Data'[Maximum Velocity (m/s)],DESC),'GPS Data'[Maximum Velocity (m/s)])

However the issue now is making the value static and not change with slicers. I don't want to edit interactions with slicers. I have tried the following DAX but the value changes with slicers.

RETURN

CALCULATE(

AVERAGEX(

TOPN(5,_table,'GPS Data'[Maximum Velocity (m/s)],DESC),'GPS Data'[Maximum Velocity (m/s)]), ALL(Dates))

Again any help is greatly appreciated

Thanks in advance.

Sean

[AlB · Accepted solution]

@Anonymous 

What slicers exactly? Are they all on from the Date table?

Variables in DAX are immutable. Their value, assigned at creation, will never change. Think of them as constants. So the CALCULATE ( ..... , ALL( ...)) will have no effect whatsoever in  _table

You would have to apply that when creating the VAR _table, not afterwards.  Something like (based on the previous code):

VAR _table =
CALCULATETABLE (
    SUMMARIZE (
        'GPS Data',
        'GPS Data'[Name],
        'GPS Data'[Session ID],
        'GPS Data'[Maximum Velocity (m/s)]
    ),
    VAR date1_ = DATE ( 2017, 07, 03 )
    VAR date2_ = DATE ( 2018, 08, 16 )
    RETURN
        FILTER (
            ALL ( 'GPS Data'[Date], 'GPS Data'[Matchday (+/-)] ),
            IF (
                'GPS Data'[Date] >= date1_ && 'GPS Data'[Date] <= date2_,
                NOT 'GPS Data'[Matchday (+/-)] IN { "MD", "PSF", "Off Season", BLANK () },
                NOT 'GPS Data'[Matchday (+/-)] IN { "PSF", "Off Season", BLANK () }
            )
        ),
    ALL ( Dates )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB The slicers are year and week no, from a date table.

Thank you for the explanation, I understand.

And again thanks very much for the solution. Now I have exactly what I need 🙂

Cheers

Sean