Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter data between two dates but not another

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
 
DAX
  • AlB's avatar
    AlB
    5 years ago

    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's avatar
    AlB
    5 years ago

    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 

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        AlB
        Community Champion
        5 years ago

        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