Calculating the duration between rows by tagname

[vrmntmtt]

Hi,

I have a problem, and I could not find it immediatly on this forum.

I have a lot of data from a light sensor, and we want to know the % of the time it was giving a signal (value = 1).

We have three sensors: LDR1, LDR and LDR3. Each time one changes, we get the values of all three sensors.

So I have to take the sum of the duration per sensor when it gives value 1, and devide it by the total reading time.

For example:

LDR2 gives value 1 from 4/08/21 at 14:48:51 until 4/08/21 14:48:58 (see picture below). So this gives a duration of 0:00:07.

Thank you in advance.

[Anonymous]

@vrmntmtt 
This is error is because you compare a integer with a text value, please share a sample in a format we can copy and past, so we can test out to reach your expected outcome.

Paul Zheng _ Community Support Team

[vrmntmtt]

Hi,

Sorry for the late reply. But I solved it a while ago.

I made a calculated column 'stopped at' and it selects the next different date. Except at the end, then Power BI uses Now

I used the following formula:

Stopped_at = IF(CALCULATE(MIN('8xLight_B2'[received_at]), FILTER('8xLight_B2',[received_at] <> EARLIER([received_at]) && [received_at] > EARLIER('8xLight_B2'[received_at])))>0,CALCULATE(MIN('8xLight_B2'[received_at]), FILTER('8xLight_B2',[received_at] <> EARLIER([received_at]) && [received_at] > EARLIER('8xLight_B2'[received_at]))),NOW())

Then I made another calculated column that takes the time difference between the start and end time.

[amitchandak]

@vrmntmtt , Create a new column like this and use that

New column =
var _max = maxx(filter(Table, [received_at] <earlier([received_at]) && [Tag] =earlier([Tag]) ),[received_at])
var _val = maxx(filter(Table, [received_at] =_max && [Tag] =earlier([Tag]) ),[value])
return
if(_val =1 && [Value]=1 , datediff(_max,received_at, second), blank())

[vrmntmtt]

@amitchandak Thank you for your fast treply.

However, the formule gives an error at the underlined text:

New column =
var _max = maxx(filter(Table, [received_at] <earlier([received_at]) && [Tag] =earlier([Tag]) ),[received_at])
var _val = maxx(filter(Table, [received_at] =_max && [Tag] =earlier([Tag]) ),[value])
return
if(_val =1 && [Value]=1 , datediff(_max,received_at, second), blank())

Update:

I was working in the query, not the data format for adding a column (my bad sorry).

But I recieved the following error while adding the column: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.