cancel
Showing results for
Did you mean:
Highlighted
Ochab1 Frequent Visitor

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

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 1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

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

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: 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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
6 REPLIES 6 Super User

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

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...

Proud to be a Datanaut!

I_Like_Pi Member

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

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.

Ochab1 Frequent Visitor

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

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'. Community Support Team

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

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: 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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Ochab1 Frequent Visitor

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

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 Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |