Looking for a faster solution: iterative search using index

[Anonymous]

Hi all!

I'm looking for a solution that will make my calculated column faster - currently I'm using a solution that works as needed, however, I bet there is a way to do it better and faster. I'm still a beginner so I would be very grateful for tips from some pros here 🙂

A little bit of context: 
Below you see the code that I use to find out whether in my log file there is a line denoting train Halt status coming up after Standstill status for the same Train nr, I use Index to maintain the order of the lines.
Halt and Standstill are binary variables, Train nr ranges from 3-6 digit train ID,  Index starts at 0. 

is there halt =

VAR _index =
LOOKUPVALUE ( 'Table'[index], 'Table'[Index], [Index] )
VAR _train =
LOOKUPVALUE ( 'Table'[Train nr], 'Table'[Train nr], [Train nr] )
VAR _haltafter =
FILTER (
'Table',
[Halt] = 1
&& [Index] > VALUE ( _index )
&& [Train nr] = VALUE ( _train )
)

RETURN
IF (
[Standstil] = 1
&& NOT ISEMPTY ( _haltafter ),
SUMX ( _haltafter, [Halt] ),
0
)

This does what I need it to do but the execution is extremely slow - can anyone suggest some improvements?

[Anonymous]

Hi @Anonymous ,
do you have a relationship between the tables you're performing the lookups from. If yes, then you can use Related() instead of Lookupvalue(). It is known that Related() is faster than Lookupvalue() under the right conditions.

Regards

[Anonymous]

Hi @Anonymous,

thank you for the reply! 

You made me realise something super important namely, I started writting this in a calculated table that was indeed related to the current table but finally ended up creating it within the current table instead. Thus, I don't need this lookup at all I can just reference a column directly... (big time facepalm)

Thanks again, seems like sometimes one just needs another persons' perspective to come back to reality 😅

[Anonymous]

No problem Iggy.
Happens to the best of us! 😄