Calculate amount of time at which a certain condition is valid

[Rajesh_G]

Hi Everyone!

I'm having an issue trying to calculate the amount of time that a certain condition is valid in a set of data. See graph below. I'm trying to calculate what amount of time the Feed Rate in this case is above 800 tph. The formula I currently have is as follows:

Time [>800 tph] = CALCULATE( ---what function can I use here?--- , 'Feed Rate'[Value]>800 )

What DAX function can I use to calculate time duration? (For which the data is above 800 tph)

[v-yuezhe-msft]

@Rajesh_G,

Please use the DAX below.

Time>800 tph = CALCULATE( COUNTA('Feed Rate'[Time]) , 'Feed Rate'[Value]>800 )

Regards,
Lydia

[Rajesh_G]

Hi Lydia

Thank you for your response. However, that seems to calculate the AMOUNT of values rather than the actual TIME that the values are above 800.

Kind regards

Rajesh

[v-yuezhe-msft]

@Rajesh_G,

What is your expected result based on the sample data I post?

Regards,
Lydia

[Rajesh_G]

It would be "2 hours" (from 7AM to 9AM) assuming that the hypothetical 10 AM would have a value below 800

[v-yuezhe-msft]

@Rajesh_G,

Create the following column.

Column = IF('Feed Rate'[Value]>800,1,0)

Then create the following measures. If the Dax don't return your expected result, please share sample data of your table here.

MinTime = CALCULATE(MIN('Feed Rate'[Time]),FILTER('Feed Rate','Feed Rate'[Column]=1))

MaxTime = CALCULATE(MAX('Feed Rate'[Time]),FILTER('Feed Rate','Feed Rate'[Column]=1))

TimeAmount = LEFT(([MaxTime]-[MinTime]),1)

Regards,
Lydia

[Rajesh_G]

Hi Lydia

Thank you for your response. 

1. Why do you use the "LEFT" function if I may ask? 

2. The method proposed does give the desired result but only if theres one continuous occurence of the condition. For instance, if in your dataset you could add that at 11 AM the value is 850 and at 12:00 its 900 and finally at 13:00 its 300 again, would this formula pick up that the total is 3 hours (2 hours between 7 and 9 and 1 hour between 11 and 12)?

Kind regards

Rajesh 

[v-yuezhe-msft]

@Rajesh_G,

The Left function is to get duration hour, otherwise the measure will show in Time formt. Please share sample data of your table and post expected result based on sample data .

Regards,
Lydia

[Rajesh_G]

Hi Lydia

Please see below an example set of data. Notice how the TimeAmount measure calculates the total time at which the condition is valid to be 6 hours whereas it is actually supposed to be 3 hours.

Kind regards

Rajesh Gowda

[v-yuezhe-msft · Accepted solution]

@Rajesh_G,

Please check DAX in the attached PBIX file.

Regards,
Lydia

[Rajesh_G]

Thank you Lydia for the great solution!