how to write a conditional for loop in DAX?

I’m using PowerBI to create a dashboard that summarizes data from a train movements simulation. (I’m a psychologist with some basic understanding of python and currently learning DAX.) Here is some background:

I use [index] to maintain the order of the rows
[Notification] is a column that contains text
[Train Nr] is a column that contains the ID of the train that the [Notification] is about
[Standstill] is a column that contains 1 if notification is info about train coming to a standstill (otherwise 0)
[Halt] is a column that contains 1 if notification is info about train stopping at a station (otherwise 0)

I can’t figure out how to build something that would work as a for loop. In a very conceptual way this would look like this:

if [standstill] = 1:
       for [index] in range([index]+500):  
          if [halt] = 1 and [train nr] is the same as in the starting row: 
                 [is there halt] = 1
                 else [is there halt] = 0

So what I’m trying to do is finding out if for any of the rows where [stanstill] = 1 any of the next 500 rows [halt] = 1 and whether that [halt] concerns the same train as the [standstill].

Can someone help? I’ve tried to modify this idea: https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314 but at this point I’m completely stuck. If any further info is necessary, let me know.

Sample data:

index	notification	train nr	standstill	halt	is there halt
1	train nr 3345 gets status standstill	3345	1	0	1
2	train nr 3345 gets status halt	3345	0	1	0
3	train nr 122 position X -> Y 89076	122	0	0	0
4	train nr 122 gets status standstill	122	1	0	1
5	train nr 54732 gets status riding	54732	0	0	0
…	…	…	…	…	…
345	train nr 122 gets status halt	122	0	1	0

The solution can be either a measure or a column >> the most important goal is to be able to summarize the total number of instances where [standstill] = 1 and [is there halt] = 0 which means that the train is in standstill because of a red light.

Answer

Ok, I will attempt to answer my own question as I found a solution. This is a calculated column:

how many halts = 

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([Standstill]=1 && not ISEMPTY(_haltafter), SUMX(_haltafter, [Halt]),0)

The result is a number of halts that occur for the same train number after the current notification that contain info about train coming to a standstill.

Maybe this will be helpful for someone

Advertisement

Answer