Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@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:
@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())
@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.
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |