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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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:

 

UniqueTripIDStopTimeRunningTimeDuration (calc column)
A17:00:00am0
A27:02:00am120
A37:04:00am240
A47:06:00am360
B18:00:00am0
B28:03:00am180
B38:05:00am300
B48:08:00am480

 

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
//)
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

@Anonymous lbendlin_0-1639229750628.png

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

 

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

@Anonymous lbendlin_0-1639229750628.png

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

 

Anonymous
Not applicable

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!!!

zhivana_0-1639339349968.png

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.