Speeding up an iterative measure

[pb123]

Hello, 

I have a measure which is trying to work out how many objects will arrive at a destination in a given window based on their distance from destination and their speed. My dataset is points for every object on each day (around 5m rows in total). The window needs to be rolling, so at all times it will show the sum arriving a specific number of days ahead.

Distance is calculated in a column, using lat lon to work out as the crow flies km distance, but the speed, and the start/end points of the window need to be adjustable, so are set up as measures based on the selected value of parameters.

To work out the time to destination I have a measure (A) taking the selected values of speed and distance.

Then to get the sum of items arriving within the window, I have another measure (B) where I am calculating the sum of objects, filtering the table where measure A is > window start & < window end

However I have almost 5m rows with plans to scale the model significantly, and this measure is very slow and fails if I try and use more than a couple of years of data. Given that the window and speed parameters need to be adjustable, I can't figure out a way of making it more efficient. 

Does anybody have any other ideas on how to approach this?

Thanks! 

[AlB]

Hi @pb123 

Do you want to share the relevant pieces of code plus a sample of your data? Otherwise we'll stay theoretical, which likely won't help much 

Please accept the solution 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.

[pb123]

Here is an example version of what I have done. Note I have already tried to speed things up by using a filtered table for the second measure to exclude some items, but unsure if this has been sued correctly.

[# sum measure] is a seperate measure that is summing the size of each object.

A

# Time to dest =

VAR distance = SELECTEDVALUE(TABLE1[DISTANCE])

VAR speed = SELECTEDVALUE(SPEED_MEASURE[Speed measure])

VAR timetodest = DIVIDE(distance , speed)

return

timetodest

B

# within range measure =

VAR curdate = SELECTEDVALUE(AXS_DAILY_STATUS[StatusDateTime])

VAR mintime = SELECTEDVALUE('window start'[window start])
VAR maxtime = SELECTEDVALUE('window end'[window end])

VAR filterdest =
FILTER(DEST_GROUPS,
NOT(DEST_GROUPS[DEST_GROUP] = 1 ||
DEST_GROUPS[DEST_GROUP] = 2))

VAR filterzone =
FILTER(CURRENT_ZONES,
CURRENT_ZONES[name] = 1 ||
CURRENT_ZONES[name] = 2 ||
CURRENT_ZONES[name] = 3)

VAR calctable = CALCULATETABLE(AXS_DAILY_STATUS,
filterdest,
filterzone,
STATUS[STATUS_GROUP] = 1)

return

Calculate(
[# sum measure],
calctable,
FILTER(AXS_DAILY_STATUS,
[# Time to dest] >= mintime),
FILTER(AXS_DAILY_STATUS,
[# Time to dest] <= maxtime)

)

[pb123]

I can't share the actual dax but will put together an example that works the same way. 

However it would be interesting to hear any ideas you have even theoretical as another way of approaching the problem!

Thanks