Returning SUM of MAX duration for bus trips to calculate total duration

[Anonymous]

Note - I want to do this in a measure, rather than a calculated column.

My data set comprises stop level data for bus routes, with timings, as well as a calculated column that calculates total elapsed duration. E.g. the following, where the result should be 840:

UniqueTripID	Stop	Time	RunningTimeDuration (calc column)
A	1	7:00:00am	0
A	2	7:02:00am	120
A	3	7:04:00am	240
A	4	7:06:00am	360
B	1	8:00:00am	0
B	2	8:03:00am	180
B	3	8:05:00am	300
B	4	8:08:00am	480

The total time for a trip is the MAX value for Calculated Duration for any given UniqueTripID (these are unique identifiers )

I've come up with a measure that works for individual level routes:

var maxtime = MAX(DATA_RADIOLA[RunningTripDuration])

var uniquetripid = MAX(DATA_RADIOLA[Unique TRIPID])

var routeresult =

CALCULATE(

MAX(

DATA_RADIOLA[RunningTripDuration]),

uniquetripid=DATA_RADIOLA[Unique TRIPID]

)

What I want to do is SUM the MAX for every unique TripID. The following does not work:

//CALCULATE(

// SUM(

// DATA_RADIOLA[RunningTripDuration]),

// DATA_RADIOLA[RunningTripDuration]=maxtime,

// DATA_RADIOLA[Unique TRIPID]=uniquetripid

//)

[lbendlin · Accepted solution]

@Anonymous 

Max Cumul := 
var a = SUMMARIZE('DATA_RADIOLA','DATA_RADIOLA'[UniqueTripID],"maxlen",MAX('DATA_RADIOLA'[RunningTimeDuration (calc column)]))
return sumx(a,[maxlen])

[Anonymous]

Wonderful, works perfectly. Here is your result compared to one I created using a helper column (which slows down calculation time), and the difference is infinitesimal.
Thanks so much!!!

[lbendlin]

you make a good point, especially on the impact of larger data sets on the performance. Instead of SUMMARIZE you can also use GROUPBY() or a variety of other approaches. In DAX Studio you can study the query plan and optimize your query as needed.