Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |