cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vrmntmtt
Frequent Visitor

Calculating the duration between rows by tagname

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.

Schermafbeelding 2021-08-24 130007.png

 

Thank you in advance.

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@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

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.
 
vrmntmtt_0-1635433436084.png

 

amitchandak
Super User
Super User

@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())



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

@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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors