Forum Discussion

vrmntmtt's avatar
vrmntmtt
Frequent Visitor
5 years ago

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.

 

Thank you in advance.

Need Help
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      vrmntmtt
      Frequent Visitor
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      vrmntmtt
      Frequent Visitor
      4 years ago

      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.