cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ochab1 Frequent Visitor
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...Smiley Mad

 

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].2017-05-26 15_07_03_PBI.png

 

 

 

 

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)

2017-05-26 15_06_09-Excel.png

 

 

 

 

Any idea if it is feasible and how to proceed ?

Many thanks in advance for your help Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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:

9.PNG

 

Steps:

1. Add Index column.

10.PNG

 

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.

11.PNG

12.PNG

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


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

9.PNG

 

Steps:

1. Add Index column.

10.PNG

 

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.

11.PNG

12.PNG

 

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
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 Smiley Happy!

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)

  

 

2017-05-29 18_29_08.png

 

 2017-05-29 11_28_23.png

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

 

Best regards

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