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 

Hmmm....  yeah each row will depend on the previous ( if there were any "resettings" previously) so that introduces a kind of recursion of circular dependency if you will that DAX is not particularly  adept at  handling. I have to give it some thought but I'm not sure it can be done like that. 

 

It could potentially be done in PQ with List.Accumulate or similar but it might be slow (if your tables are big). Would doing it in M be a possibility?

 

In any case, the neatest option would probably be to clean up the data first in PQ. You mention the queue going negative is actually due to some spurious double counting. Can that not be eliminated with some preprocessing? And then you forget about it in your analysis...

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

Would be great if PowerQuery is able to achieve this, have to admit i'm a relaitive novice to M and just perform basic tasks within it. I assume i'd have to pivot the data (the tables in included in the file are just for demonstration) then create the current measures i have in custom columns? 

 

Thanks for the responses so far!