topic Re: Filter data between two dates but not another in DAX Commands and Tips

Filter data between two dates but not another

Anonymous — Wed, 11 Nov 2020 16:25:46 GMT

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

Re: Filter data between two dates but not another

AlB — Wed, 11 Nov 2020 20:16:27 GMT

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

Re: Filter data between two dates but not another

Anonymous — Thu, 12 Nov 2020 11:21:18 GMT

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

Re: Filter data between two dates but not another

AlB — Thu, 12 Nov 2020 15:32:33 GMT

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

Re: Filter data between two dates but not another

Anonymous — Thu, 12 Nov 2020 12:26:16 GMT

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