Forum Discussion
Need help with grouping and sorting
Our goal is to compute how many times a device has been turned on within a case.
The data looks like this in an events table.
The device sends a message once per second (there could be a lage but timestamp would still be unique). As long as the device is ON, the deviceON bit will stay true. When it's turned off, the bit will flip to false. A device can be turned off multiple times with a case (Case Id is unique for case) .Our goal is to compute how many times a device has been turned on within a case as shown in the output.
Can someone please provide guidance on how to do this in PowerBI?
CaseId deviceON Timestamp
1 True 08/07/2018 11:22:14
1 True 08/07/2018 11:22:15
1 False 08/07/2018 11:22:16
1 False 08/07/2018 11:22:17
1 True 08/07/2018 11:22:18
1 True 08/07/2018 11:22:19
2 True 08/07/2018 12:20:10
2 True 08/07/2018 12:20:11
2 False 08/07/2018 12:20:12
2 False 08/07/2018 12:20:13
2 False 08/07/2018 12:20:14
2 False 08/07/2018 12:29:15
Desired output
CaseId Number_Of_Times_Device_Was_ON
1 2
2 1
Take a look at this file. I had to do a bit in Power Query and a bit in DAX so it wouldn't double-count the TRUE() records if the previous record was true.
I used Power Query to add an index column to the file, then used DAX to move up and down the index column.
Note: this is not bullet proof! It assumes the CaseID and TimeStamps are sorted ascending before the index is added.
The DAX function that does this:
Turned On = IF( Data[CaseID] <> LOOKUPVALUE(Data[CaseID],Data[Index],Data[Index]-1) && Data[DeviceOn] = TRUE(), 1, IF( Data[DeviceOn] = TRUE() && LOOKUPVALUE(Data[DeviceOn],Data[Index],Data[Index] - 1) = FALSE(), 1, 0 ) )It is a calculated column in the table that sets [Turned on] to 1 if it is the same device, was off before, and was not the first row. If it is the first row for the device and it is on, it flips the bit to 1 too.
9 Replies
Hi shilpisingal
Try this measure.
Measure = CALCULATE(COUNT(Table1[DeviceOn]), FILTER( Table1,CaseID= MAX(Table1[CaseID]) && Table1[DeviceOn] = "True") )I havent tested this, it should work.but if you see any issues, let me know.
Thanks
Raj
Hi Raj,
Thanks for trying. I tried your suggestion but didn't get the correct response.
Also don't understand how this will work without including the Timestamp in the measure?
Measure = CALCULATE(COUNT('DeviceEvents'[deviceON]),
FILTER( 'DeviceEvents','DeviceEvents'[CaseId]= MAX('DeviceEvents'[CaseId]) && 'DeviceEvents'[deviceON] = "True")
)The output is coming as
CaseId Measure
12 2
Take a look at this file. I had to do a bit in Power Query and a bit in DAX so it wouldn't double-count the TRUE() records if the previous record was true.
I used Power Query to add an index column to the file, then used DAX to move up and down the index column.
Note: this is not bullet proof! It assumes the CaseID and TimeStamps are sorted ascending before the index is added.
The DAX function that does this:
Turned On = IF( Data[CaseID] <> LOOKUPVALUE(Data[CaseID],Data[Index],Data[Index]-1) && Data[DeviceOn] = TRUE(), 1, IF( Data[DeviceOn] = TRUE() && LOOKUPVALUE(Data[DeviceOn],Data[Index],Data[Index] - 1) = FALSE(), 1, 0 ) )It is a calculated column in the table that sets [Turned on] to 1 if it is the same device, was off before, and was not the first row. If it is the first row for the device and it is on, it flips the bit to 1 too.
Thank you so much for taking the time to respond to my solution. I started following your steps and getting stuck at how you created the DAX function for "Turned On". I tried creating a measure on the table and also a new custom column but in both situations I'm running into this error
"single value for column 'DeviceOn' in table 'pumpStatusChanged' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum
I noticed you've a summation sign on the "Turned On" attribute. I can't figure out how you get that. Can you please tell how you created the column "Turned On". Thanks a lot
Anonymous
I took it to mean that when the same device had TRUE 2+ records in a row, that wasn't being turned on, it was just still on, and didn't need to be counted as a Turn On event.
oh got it edhans. Thanks. In that case, my approach wont work, which was counting all occurances .
shilpisingal - Please try edhans approach.
Thanks
Raj
