Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIn 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?
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingoh got it @edhans. Thanks. In that case, my approach wont work, which was counting all occurances .
@shilpisingal - Please try @edhans approach.
Thanks
Raj
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
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
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
User | Count |
---|---|
92 | |
87 | |
77 | |
73 | |
68 |
User | Count |
---|---|
116 | |
107 | |
88 | |
65 | |
63 |