Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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:
Solved! Go to Solution.
@Anonymous
Max Cumul :=
var a = SUMMARIZE('DATA_RADIOLA','DATA_RADIOLA'[UniqueTripID],"maxlen",MAX('DATA_RADIOLA'[RunningTimeDuration (calc column)]))
return sumx(a,[maxlen])
@Anonymous
Max Cumul :=
var a = SUMMARIZE('DATA_RADIOLA','DATA_RADIOLA'[UniqueTripID],"maxlen",MAX('DATA_RADIOLA'[RunningTimeDuration (calc column)]))
return sumx(a,[maxlen])
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!!!
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.