Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I am trying to summarise a data table using the following DAX;
Solved! Go to Solution.
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
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
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
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;
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
@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
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |