Forum Discussion

Benji_B's avatar
Benji_B
Frequent Visitor
5 years ago
Solved

Queue Population Reset

Hi all,   I am attempting to use measures to calculate the number of people in a queue at any given point at time. I have 'In' set as the number of people entering the process (let's say it's a sho...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Benji_B ,

     

    First convert measure RollingSumofCountIn; RollingSumofCountOut; QueuePopulation into columns;

    Then create 2 columns as below:

     

    flag = 
var _prevalue=CALCULATE(MIN('example'[Column_Q]),FILTER('example','example'[Time]<=EARLIER(example[Time])&&'example'[Column_Q]<0))
var _time=CALCULATE(min('example'[Time]),FILTER('example','example'[Column_Q]=_prevalue))
Return
IF('example'[Time]=_time&&'example'[Column_Q]<0,1,0)
    col_Adjust = 
var _time=CALCULATE(MAX('example'[Time]),FILTER('example','example'[Time]<=EARLIER(example[Time])&&'example'[flag]=1))
var _q=CALCULATE(MAX('example'[Column_Q]),FILTER('example','example'[Time]=_time))
 Return
 IF('example'[Time]<_time,'example'[Column_Q],
 IF('example'[Time]=_time,0,
'example'[Column_Q]-_q))

     

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

AlB's avatar
AlB
Community Champion
5 years ago

Benji_B 

My apologies. the file is fine. I must have checked the wrong one earlier.

Can you explain what exactly you mean by 'reset' everytime the queue is at 0 ?

If you provide the expected result for your sample data above (at least a few rows that include that resetting to zero) it should be enough

If what you want is what I suspect, it might be tricky as it would imply recursion

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

 

 

  • Benji_B's avatar
    Benji_B
    Frequent Visitor
    5 years ago

    No worries 🙂

     

    i'll try to talk through what i mean, i expect this is probably not possible with measures..

     

    Sorry i realised the columns below have no titles! They are..

    Time; CountIn; CountOut; RollingSumofCountIn; RollingSumofCountOut; QueuePopulation (the sum of rolling count in minus count out)

     

    If you look at the 06:00:00 row the Queue goes to -7 because of a data error in the 'CountOut' column (a draw back of the system that registers the data as it's basically a laser that people pass under, people can walk back and forth skewing the data).

     

    I want 06:00 to show 0 as the Queue which can be achieved with a simple IF statement but then the 06:15 > queue length will continue to be incorrect as it is derived from the cumulative totals. I.e. the queue length at 06:15 should be 5 not -2 as it needs to ignore the -7 that preceeds it.

     

     

    Ben.