Create a Power query logic to find Open Status more than 1 hour

[sivarajan21]

Hi Team,

I have the door table

Here I want to find that whether door is being open continuously(having open status & not closed) for more than 1 hour for each DeviceId.

Data Description:

The Status column shows whether door is open or Closed.

CreatedOn column shows time duration.

DeviceId column shows Deviceid.

My first requirement is:

We need to add the minutes(createdon) between each row for same deviceid where the status = open. But where a status = closed (if closed comes in between open) we reset the number of minutes.

So we sum by the previous value where status = Open -
Something like If current status = open and previous status = open then sum the time and do the below:
Sum(previous value + new sum) 

Where/if status = closed set time count = 0

Sum(previous value + new sum) is going to take into account only the consecutive open status >60 min for same divice id

whenever status is closed, this Sum(previous value + new sum) will be reset to/equal to 0

So on the first open status, the count will be 0. Then the next open status it will be time between 2 open statuses, then on the third it will be the time between two open statuses add the previous time.but any close status then time is reset.

For example of above description:

The brown color highlight does not alert because it did not complete 60 min as Closed came up.

Could you please help me with a power query logic/dax logic?

Second Requirement:

This is only a sample data. But we have billions of rows of data in our dataflow that comes from SQL views.

@marcorusso  sir came with a brilliant idea of below:
'The suggestion is not to use DAX for this calculation. You can precalculate the maximum open state duration by preprocessing your data (SQL, Power Query, Spark, anything else...). Using DAX does not make much sense because performance will quickly decrease when you try to compute the same calculation in every report through a measure.

So can you help me prepare data upfront. we need to solve the problem with a with a proper data model. for example, create a table with the longest open events upfront (in whatever technique you are comfortable with)'.

Could you please help me to achieve this?

PFA file here B&M.pbix

Please let me know if you need further info!

Thanks in advance!

@marcorusso @tharunkumarRTK @Greg_Deckler @Ahmedx @v-linyulu-msft @v-yaningy-msft 

[tharunkumarRTK · Accepted solution]

@sivarajan21 

I wrote the logic as per my understanding. 

Here is the link to download the file: 

Table Name: "Door Power Query"

Hope it helps

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[sivarajan21]

Hi Tharun,

This is an amazing solution and wonderfully crafted!🤩

You are truly a masterpiece and i still can't believe that this issue got resolved.

You came like a saviour for this community!

I will mark it as solution

Many thanks!