- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Measure to calculate the number of trucks waiting ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Ochab1

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-26-2017
06:20 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-shex-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-27-2017
02:41 AM

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

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

If this post

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

6 REPLIES 6

Greg_Deckler

Super User

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-26-2017
07:09 AM

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.

Proud to be a Datanaut!

I_Like_Pi

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-26-2017
07:21 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-26-2017
07:34 AM

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

v-shex-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-27-2017
02:41 AM

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

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

If this post

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

Ochab1

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-29-2017
09:38 AM

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

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-30-2017
11:19 PM

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

If this post

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