Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Fabric Community site will be in read-only mode on Monday, Feb 24 from 12:01 AM to 8 AM PST for scheduled upgrades.

Reply
sivarajan21
Post Partisan
Post Partisan

Create a Power query logic to find Open Status more than 60 minutes

Hi Team,

 

I have the door table

 
sivarajan21_0-1739519853253.png

 

 

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:

sivarajan21_1-1739519853250.png

 

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!

 @tharunkumarRTK  @v-linyulu-msft @v-yaningy-msft 

1 ACCEPTED SOLUTION
wini_R
Solution Supplier
Solution Supplier

Hey @sivarajan21,

This is related to your first requirement. Not sure how efficient the code is on big dataset but you can give it a try. Hopefully I haven't missed anything on the logic

 

let
    Source = //[your Door table]//,
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Extracted Date Time" = Table.TransformColumns(#"Promoted Headers", {{"CreatedOn", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Date Time",{{"CreatedOn", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DeviceId"}, {{"all0", each Table.AddIndexColumn(Table.Sort(_, {{"CreatedOn", Order.Ascending}}), "index0", 0, 1), type table}}),
    #"Added Custom" = Table.Combine (Table.AddColumn(#"Grouped Rows", "all1", each Table.NestedJoin([all0], {"index0"}, Table.AddIndexColumn([all0], "index1", 1, 1), {"index1"}, "all2", JoinKind.LeftOuter))[all1]),
    #"Expanded all2" = Table.ExpandTableColumn(#"Added Custom", "all2", {"DeviceId", "Status", "CreatedOn"}, {"DeviceIdPrev", "StatusPrev", "CreatedPrev"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded all2", "Duration", each if [Status] = [StatusPrev] then [CreatedOn]-[CreatedPrev] else null),
    #"Grouped with RT" = Table.Combine (Table.Group(#"Added Custom1", {"DeviceId", "Status"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"DuratRT", each fxRT("RunnTot", _, "Duration")} }, 0)[DuratRT]),
    #"Added Over1h" = Table.AddColumn(#"Grouped with RT", "Over1h", each if [DeviceId] = [DeviceIdPrev] and [StatusPrev] = "Open" and [Status] = [StatusPrev] and [RunnTot] > #duration(0,1,0,0) then "over" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Over1h",{"Morethan1hour", "index0"})
in
    #"Removed Columns"

 

Running total function (fxRT) used in one of the steps

 

( RTColumnName as text, MyTable as table, ValueColumn as text) =>
let
    Source = MyTable,
    BuffValues = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
    RunningTotal = 
      List.Generate ( 
        () => [ RT = BuffValues{0}, RowIndex = 0 ],
        each  [RowIndex] < List.Count(BuffValues),
        each  [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
                RowIndex = [RowIndex] + 1 ],
        each  [RT] ),
    #"Combined Table + RT" = 
      Table.FromColumns( 
        Table.ToColumns( MyTable )   
           & { Value.ReplaceType( RunningTotal, type {Int64.Type} ) } , 
        Table.ColumnNames( MyTable ) & { RTColumnName } )
in
    #"Combined Table + RT"

 

 

Pbix file: https://www.dropbox.com/scl/fi/himfr8jllkru8b8lqgt6l/B-M_over1h.pbix?rlkey=toruni4qtwtjqqeff4cpx52ja...

View solution in original post

6 REPLIES 6
dufoq3
Super User
Super User

Hi @sivarajan21

 

Two questions:

  1. do you need all rows in power bi or is it enough to keep just alert rows?
  2. what to you want to do with last 2 rows in this case?

dufoq3_0-1739551836574.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi @dufoq3 ,

 

Thanks for your quick response!

Answers to your questions:

1)Yes I need all rows in pbi

2)just keep them like other rows

 

thanks in advance

for number 2. Do you want to mart any of last 2 rows as alert?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi @dufoq3 

 

Thanks for your quick response!☺

this is resolved now!

 

Thanks

wini_R
Solution Supplier
Solution Supplier

Hey @sivarajan21,

This is related to your first requirement. Not sure how efficient the code is on big dataset but you can give it a try. Hopefully I haven't missed anything on the logic

 

let
    Source = //[your Door table]//,
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Extracted Date Time" = Table.TransformColumns(#"Promoted Headers", {{"CreatedOn", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Date Time",{{"CreatedOn", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DeviceId"}, {{"all0", each Table.AddIndexColumn(Table.Sort(_, {{"CreatedOn", Order.Ascending}}), "index0", 0, 1), type table}}),
    #"Added Custom" = Table.Combine (Table.AddColumn(#"Grouped Rows", "all1", each Table.NestedJoin([all0], {"index0"}, Table.AddIndexColumn([all0], "index1", 1, 1), {"index1"}, "all2", JoinKind.LeftOuter))[all1]),
    #"Expanded all2" = Table.ExpandTableColumn(#"Added Custom", "all2", {"DeviceId", "Status", "CreatedOn"}, {"DeviceIdPrev", "StatusPrev", "CreatedPrev"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded all2", "Duration", each if [Status] = [StatusPrev] then [CreatedOn]-[CreatedPrev] else null),
    #"Grouped with RT" = Table.Combine (Table.Group(#"Added Custom1", {"DeviceId", "Status"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"DuratRT", each fxRT("RunnTot", _, "Duration")} }, 0)[DuratRT]),
    #"Added Over1h" = Table.AddColumn(#"Grouped with RT", "Over1h", each if [DeviceId] = [DeviceIdPrev] and [StatusPrev] = "Open" and [Status] = [StatusPrev] and [RunnTot] > #duration(0,1,0,0) then "over" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Over1h",{"Morethan1hour", "index0"})
in
    #"Removed Columns"

 

Running total function (fxRT) used in one of the steps

 

( RTColumnName as text, MyTable as table, ValueColumn as text) =>
let
    Source = MyTable,
    BuffValues = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
    RunningTotal = 
      List.Generate ( 
        () => [ RT = BuffValues{0}, RowIndex = 0 ],
        each  [RowIndex] < List.Count(BuffValues),
        each  [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
                RowIndex = [RowIndex] + 1 ],
        each  [RT] ),
    #"Combined Table + RT" = 
      Table.FromColumns( 
        Table.ToColumns( MyTable )   
           & { Value.ReplaceType( RunningTotal, type {Int64.Type} ) } , 
        Table.ColumnNames( MyTable ) & { RTColumnName } )
in
    #"Combined Table + RT"

 

 

Pbix file: https://www.dropbox.com/scl/fi/himfr8jllkru8b8lqgt6l/B-M_over1h.pbix?rlkey=toruni4qtwtjqqeff4cpx52ja...

Hi @wini_R ,

 

will get back on this

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors