Queue Population Reset

[Benji_B]

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 shop checkout area as an example) and 'Out' as the number of people exiting the queue at the end of the checkout.

The challenge i have is due to the nature of the system that captures the data it 'double counts' some people 'Out' of the system. I have a measure that counts the cumuliative total of people 'In' and the same for the number of people 'Out'. I then just subtract 'Out' from 'In' to see the queue but the data discrepencies will make the queue go into - figures.

What i need is for it to 'reset' everytime the queue is at 0, any help on some creative ways to achieve this would be greatly appreciated 🙂 

pbix > https://1drv.ms/u/s!Ao3TCEXXyLXThSwNZgOpmag9b5ys?e=CpneTi

[v-kelly-msft · Accepted solution]

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

[Benji_B]

Wow this is fantastic, thank you so much!

[AlB]

@Benji_B 

Have a look at this:

https://community.powerbi.com/t5/Power-Query/Reset-count-of-ID-once-they-ve-all-been-seen-count-how-...

It dealt with a similar issue (It gets complex though)

I insist the (most likely) best way forward would be to address those inconsistencies in the data, clean them up in PQ. If you describe what they are all about maybe we can look into it

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 

[AlB]

@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]

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!

[AlB]

@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]

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.

[AlB]

Hi @Benji_B 

The uploaded file doesn't seem to have any information on time, nor does it include the code you've already put together.

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]

Hi @AlB Thank's for the quick response!

Sorry just to clarify - the file doesn't show the table in the screen shot in the original post?