Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
seanmcc
Helper I
Helper I

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
 
2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @seanmcc 

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 

SU18_powerbi_badge

View solution in original post

@seanmcc 

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 ( 20170703 )
    VAR date2_ = DATE ( 20180816 )
    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 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @seanmcc 

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 

SU18_powerbi_badge

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

@seanmcc 

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 ( 20170703 )
    VAR date2_ = DATE ( 20180816 )
    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 

SU18_powerbi_badge

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors