Measure to calculate the number of trucks waiting in a queue - recursive calculation

[Ochab1]

Dear PowerBI community,

I'm a beginner with PowerBI and DAX.

I have the following problem and, after searching on this forum & internet, I cannot find my way out...

For each time period (by hour here), I have a measure to define the number of trucks who arrived in a warehouse [12_Actual Load] and the available capcity, ie the max number of trucks which can be undocked during the period [13_ActualCapacity].

The number of trucks entering/leaving the queue is then [31_In/Out waiting trucks] = [12_ActualLoad] - [11_ActualCapacity].

I would like a measure to calculate the number of trucks waiting in the queue at the end of each time period.

• For the first time period (1), it is Queue(1) = Max( 0 , [31_In/Out waiting trucks](1) )
• For the next time periods, Queue(n+1) = Max( 0 , [31_In/Out waiting trucks](n+1) + Queue(n)

Any idea if it is feasible and how to proceed ?

Many thanks in advance for your help 

[Anonymous]

Hi @Ochab1 Did you use the recursive function in the end or did you manage to calculate the result in DAX?

[v-shex-msft · Accepted solution]

Hi @Ochab1,

You can try to write a recursive function in power query, then invoke it at original table.

Below is the sample.

Table:

Steps:

1. Add Index column.

2. Add a blank query to write recursive function, name it to Test.

let
    Test= (source as table, index) =>
    let
        Temp = if index < 1 then 0 else source{index}[Value] + Test(source,index-1),
        Result= if Temp <0 then 0 else Temp
    in 
        Result
in
    Test

3. Add custom column to invoke custom function.

4. Remove Index column.(option)

Regards,

Xiaoxin Sheng

[Ochab1]

Thanks a lot for all these explanations @v-shex-msft !

Unfortunately, I do not have the data (I get a file "Raw Data" with all the trucks for a week and then aggregate them by timeslot to calculate the "Actual load". And I get the "Actual Capacity" from another data source.

I tried to apply your method to a merged table after having grouped my raw data by timeslot to have "actual load" and "actual capacity" by timeslot and to start with the same table than your first printscreen. It works on the preview but I need ~10min to load a preview and then when I "close and apply", it get stucks in "Apply query changes" and keeps calculating up to 5 GB from my file (both files "raw data" and "capacity" are below 80kb)

 

Any idea if I could do the same type of calculation using measures & calculated columns ?

Best regards

[v-shex-msft]

Hi @Ochab1,

>>It works on the preview but I need ~10min to load a preview and then when I "close and apply", it get stucks in "Apply query changes" and keeps calculating up to 5 GB from my file (both files "raw data" and "capacity" are below 80kb)

For the performing reason, I don't recommend you use recursive calculation function on huge amount of records.

I'd like to suggest you direct calculate the result in excel instead of use recursive calculation in power query.(in excel you can got the row content from specify cell, it cost less than which recursive calculation spend, so it will be suitable for your scenario)

Then import these data to power bi.

Regards,

Xiaoxin Sheng

[I_Like_Pi]

I am repliying so that I can keep track of this very interesting Q. I was going to suggest a cumulative total column of 31 (that for my novice skills was complex) but the max 0 formula points out that you can't go negative and therefore need to account for it which my suggestion would not have handled. So hopefully a hero DAXer comes along.

[Greg_Deckler]

That seems like a Cummulative Total or Running Total pattern. 

http://www.daxpatterns.com/cumulative-total/

There is a Running Total quick measure that you might be able to use.

If you truly need recursion, I have done recursion in M, but I don't think that is really what you need.

https://community.powerbi.com/t5/Community-Blog/Using-Recursion-to-Solve-Hex-to-Decimal-Conversion/b...

[Ochab1]

Thanks a lot for your reply @Greg_Deckler . I do not think that this can be calculated with a cumulative total, because if I'm in an "overcapacity" situation, then the queue will stay to zero and I will kind of 'waste' capacity (ex: from 09:00 to 13:00).

But once that there are more trucks arriving (actual load) in a period than 'actual capacity', the queue will start to grow. And enventually decrease up to zero on periods when there will be more 'capacity' than 'load'.