Need help with grouping and sorting

[shilpisingal]

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

[Anonymous]

In your example, case 1 was true for 4 times and 2 was True for 2 times. But, in your output , the count to be 2 &1. Could you please explain this?

[edhans]

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

[shilpisingal]

@edhans

Never mind. I figured out my mistake. I created a new Column and copied the DAX formula in there. Now I'm all set. Thank you so much for your help. Couldn't have done it without your guidance.

[edhans]

---

Never mind. I figured out my mistake. I created a new Column and copied the DAX formula in there. Now I'm all set. Thank you so much for your help. Couldn't have done it without your guidance.

---

Yup. Not a huge fan of calculated columns, but for a quick solutoin it seemed better than a more complex Power Query operation.

Glad you got it sorted out and thanks for marking it as a solution.

[Anonymous]

oh got it @edhans. Thanks. In that case, my approach wont work, which was counting all occurances .

@shilpisingal - Please try @edhans approach.

Thanks
Raj

[edhans · Accepted solution]

@shilpisingal,

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.

[shilpisingal]

@edhans@

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]

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

[shilpisingal]

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