Dax to find open status for more than 1 hour

[sivarajan21]

Hi Team,

I have the below table:

Here I want to find that whether door is being open for more than 1 hour or not.

The Status column shows whether door is open or not.

CreatedOn column shows time duration.

Could you please help me create a dax logic to achieve this?

FYI, PFA file here B&M.pbix

Thanks in advance!

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

[marcorusso · Accepted solution]

It depends on your requirements. If you see values in other statuses, you should create them as well, but one row for the entire duration of the status.

[sivarajan21]

Hi @marcorusso  Sir ,

This thread is cluttered.

I will open a new query to get more clarifications by closing this one.

Thnaks in advance!

[sivarajan21]

Hi @marcorusso sir,

Thanks for your quick response!

Just to confirm, when you say 'but one row for the entire duration of the status.'

Any tiny examples that you can give me for my understanding to take it forward?

Because, the data i have does contain one row for every 15 min stating open/Closed.

Thanks in advance!

@marcorusso 

[mark_endicott]

@sivarajan21 - Use the DAX below in a measure, it will give you a 1 if the door has been open for 1 hour or more, and a 0 if not. 

Just replace 'Table' with your table name.

VAR _Table =
    CALCULATETABLE (
        ADDCOLUMNS (
            'Table',
            "Time Diff", DATEDIFF ( SELECTEDVALUE ( 'Table'[CreatedOn] ), NOW (), HOUR )
        ),
        FILTER ( VALUES ( 'Table'[Status] ), 'Table'[Status] = "Open" )
    )
RETURN
    IF ( SUMX ( FILTER ( _Table, [Time Diff] > 1 ), 1 ) = 1, 1, 0 )

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[mark_endicott]

@sivarajan21 - Given that you have kudo'd I take it you have tried my suggestion and it works? See below to show it working for me (check clock in bottom right to verify time):

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[sivarajan21]

Hi @mark_endicott ,

Your solution doesn't work with the data i have:

Now, I have created a calculated column & dax to  find whether its open for more than 1 hour. But i am not sure whether they are correct or not?
please help

PFA here B&M.pbix

@marcorusso thanks for your solution. I will make sure to implement the logic(this calculated column-'Morethan1hour') in my dataflow or SQL. But i want to make sure they return the correct results

Thanks in advance!

@mark_endicott @marcorusso 

[mark_endicott]

@sivarajan21 - it doesn't work because you have used my DAX in a calculated column, I specified that you should put this in a Measure. You would need to do this so it dynamically handles  NOW() which gives the current date time. Calculated columns have a different context to measures, and they are static until the model is refreshed, therefore the time in NOW() would be stored at the point of the table refresh. This would stop your calculation from being dynamic and potentially provide false answers when the time changes.  

This would also be an issue if you were to pre-calculate duration using getdate() at the database level. 

Please use a measure for my DAX, you will then see it works. 

[sivarajan21]

Hi @mark_endicott ,

Good Day!

I have used your measure in dax and not in calculated columns. PFA screenshot below

Then also, its not working.

Also, the requirement is to use Prev Createdon date - Current Createdon date to find datediff and not current time(NOW() Function). Apologies for not being specific.

Thanks in advance!

[mark_endicott]

@sivarajan21 - You have not told me why it is not working. Your screenshot does not show any error, and I cannot see a visual to see that it's not working. If you do not need to use NOW() then your measure DAX just needs to be changed to the below:

VAR _Table =
    CALCULATETABLE (
        ADDCOLUMNS (
            'Door',
            "Time Diff", DATEDIFF ( SELECTEDVALUE ( 'Door'[Prev CreatedOn] ), SELECTEDVALUE ( 'Table'[CreatedOn] ), HOUR )
        ),
        FILTER ( VALUES ( 'Door'[Status] ), 'Door'[Status] = "Open" )
    )
RETURN
    IF ( SUMX ( FILTER ( _Table, [Time Diff] > 1 ), 1 ) = 1, 1, 0 )

[sivarajan21]

Hi @mark_endicott 

Apologise! thanks for your quick response

Will test this and get back😊

Thanks in advance!

[sivarajan21]

Hi @mark_endicott ,

Thanks for your quick response!

Give me sometime for diff testing cases and will get back to you😊

Thanks in advance

[powerbidev123]

Hi  @sivarajan21 

You can create a calculated column in Power BI using DAX to determine if the door has been open for more than 1 hour.

Duration_Hours = DATEDIFF( TableName[CreatedOn], TableName[UpdatedOn], HOUR )

This calculates the time difference in hours between CreatedOn and UpdatedOn.

Now, create another calculated column to check if the duration exceeds 1 hour:

IsOpenMoreThan1Hour = IF( TableName[Duration_Hours] > 1, "Yes", "No" )

[Fowmy]

@sivarajan21 

To caulcate the time difference, which columns should be used? 

[marcorusso]

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.

On Feb 25, you will find an article on www.sqlbi.com, which discusses a somewhat similar scenario. Even if you will find transformations in DAX (using calculated tables, not measures), the suggestion is to prepare data upfront with a proper data model if you have millions of rows or more.

[sivarajan21]

Hi @marcorusso ,

Thanks for your quick response!

When you say, Feb 25 is it 2024 or 2025?

Also, I am keen on this article as i have billions of rows for my data.

Please advise! 

If possible forward the article.

Thanks in advance!

@marcorusso 

[marcorusso]

@sivarajan21 I cannot provide the article until it is published. However, you have to solve the problem with a proper data model, create a table with the longest open events upfront (in whatever technique you are comfortable with), and then query the table in DAX. If you have billions of rows, that's the only way; the article will not help you much.

[sivarajan21]

Hi @marcorusso ,

Thanks for your quick response!

Just to confirm, you mean to say only table with status as Open and not any other status?

Thanks in advance!