The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
We have a report where you can select an origin and destination stop to calculate the average travel time between those stops using the measure below. Is there any way to speed up the measure? WkOperatingDayDate, TechnicalLineNumber, JourneyGid and VehicleIdentity uniquely identifies a journey and SequenceNumber is the ordinal number of a stop on the journey route.
AvgActualTravelDuration(s) =
VAR originStop = SELECTEDVALUE(OriginStopArea[StopAreaGid])
VAR destinationStop = SELECTEDVALUE(DestinationStopArea[StopAreaGid])
VAR originTable =
SELECTCOLUMNS(
CALCULATETABLE(
F_TravelStop,
D_StopArea[StopAreaGid]=originStop
),
"WkOperatingDayDate", [WkOperatingDayDate],
"TechnicalLineNumber", RELATED(D_Line[TechnicalLineNumber]),
"JourneyGid", [JourneyGid],
"VehicleIdentity", [VehicleIdentity],
"OriginSequenceNumber", [SequenceNumber],
"OriginActualTravelDurationRunningTotal", [ActualTravelDurationRunningTotal]
)
VAR destinationTable =
SELECTCOLUMNS(
CALCULATETABLE(
F_TravelStop,
D_StopArea[StopAreaGid]=destinationStop
),
"WkOperatingDayDate", [WkOperatingDayDate],
"TechnicalLineNumber", RELATED(D_Line[TechnicalLineNumber]),
"JourneyGid", [JourneyGid],
"VehicleIdentity", [VehicleIdentity],
"DestinationSequenceNumber", [SequenceNumber],
"DestinationActualTravelDurationRunningTotal", [ActualTravelDurationRunningTotal]
)
VAR joinedTable =
FILTER(
NATURALINNERJOIN(
originTable,
destinationTable
),
[OriginSequenceNumber] < [DestinationSequenceNumber]
)
RETURN
AVERAGEX(
joinedTable,
([DestinationActualTravelDurationRunningTotal] - [OriginActualTravelDurationRunningTotal]))
The first thing you should do is to download Dax Studio, an invaluable tool that will give you detailed feedback on how your measure executes https://daxstudio.org/
The next thing is to watch https://www.sqlbi.com/tv/my-power-bi-report-is-slow-what-should-i-do/....which will give you some good hints about possible improvements.
I'm a personal Power Bi Trainer I learn something every time I answer a question
The Golden Rules for Power BI
Help when you know. Ask when you don't!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
27 | |
16 | |
15 | |
14 |
User | Count |
---|---|
47 | |
44 | |
41 | |
29 | |
22 |