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.
Hi there,
First time posting here so, bear with me. I tried to find answers to my problems but I couldn't find the one I could apply to my situation.
I am trying to calculate number of alarms, downtime and other measures from the data source that contains daily csv files. Some machines' alarms last more than a day but they are captured as separate entry in the next csv logs. Small part of my data looks like this:
Machine ID | Alarm Type | START_DT | END_DT | Downtime |
456 | F | 2/03/2020 0:00 | 2/03/2020 3:56 | 3.95 |
456 | W | 2/03/2020 0:00 | 2/03/2020 3:56 | 3.95 |
774 | F | 2/03/2020 5:27 | 2/03/2020 6:30 | 1.06 |
774 | W | 2/03/2020 5:27 | 2/03/2020 6:30 | 1.06 |
1339 | B | 29/02/2020 0:00 | 1/03/2020 0:00 | 24.00 |
1339 | B | 1/03/2020 0:00 | 2/03/2020 0:00 | 24.00 |
1339 | B | 2/03/2020 0:00 | 3/03/2020 0:00 | 24.00 |
1339 | B | 3/03/2020 0:00 | 4/03/2020 0:00 | 24.00 |
1339 | B | 4/03/2020 0:00 | 5/03/2020 0:00 | 24.00 |
3288 | F | 29/02/2020 7:45 | 29/02/2020 12:31 | 4.77 |
3479 | B | 29/02/2020 0:00 | 29/02/2020 13:40 | 13.67 |
4735 | F | 29/02/2020 8:00 | 29/02/2020 18:44 | 10.72 |
5412 | B | 29/02/2020 0:00 | 1/03/2020 0:00 | 24.00 |
5412 | B | 1/03/2020 0:00 | 2/03/2020 0:00 | 24.00 |
5412 | B | 2/03/2020 0:00 | 3/03/2020 0:00 | 24.00 |
5412 | B | 8/03/2020 0:00 | 9/03/2020 0:00 | 24.00 |
5412 | B | 9/03/2020 0:00 | 10/03/2020 0:00 | 24.00 |
5412 | B | 10/03/2020 0:00 | 10/03/2020 10:55 | 10.91 |
5412 | F | 10/03/2020 10:55 | 10/03/2020 11:03 | 0.14 |
5851 | B | 29/02/2020 15:25 | 29/02/2020 19:51 | 4.44 |
6405 | B | 29/02/2020 0:00 | 29/02/2020 7:00 | 7.00 |
I'd like to merge a machine with the same alarm type into one row. I have years worth of logs with 35k rows in each log. So, I am not sure in which way I should merge the rows (M code or DAX measure). The result should be like this:
Machine ID | Alarm Type | START_DT | END_DT | Downtime |
6405 | B | 29/02/2020 0:00 | 29/02/2020 7:00 | 7.00 |
1339 | B | 29/02/2020 0:00 | 5/03/2020 0:00 | 120 |
3479 | B | 29/02/2020 0:00 | 29/02/2020 13:40 | 13.67 |
5412 | B | 29/02/2020 0:00 | 3/03/2020 0:00 | 72.00 |
3288 | F | 29/02/2020 7:45 | 29/02/2020 12:31 | 4.77 |
4735 | F | 29/02/2020 8:00 | 29/02/2020 18:44 | 10.72 |
5851 | B | 29/02/2020 15:25 | 29/02/2020 19:51 | 4.44 |
456 | F | 2/03/2020 0:00 | 2/03/2020 3:56 | 3.95 |
456 | W | 2/03/2020 0:00 | 2/03/2020 3:56 | 3.95 |
774 | F | 2/03/2020 5:27 | 2/03/2020 6:30 | 1.06 |
774 | W | 2/03/2020 5:27 | 2/03/2020 6:30 | 1.06 |
5412 | B | 8/03/2020 0:00 | 10/03/2020 10:55 | 58.91 |
5412 | F | 10/03/2020 10:55 | 10/03/2020 11:03 | 0.14 |
The second issue I have is filtering downtime and other measures monthly. Since some alarms last for days, their duration could span across 2 months. How can I filter it in such a way that my dashboard shows the correct measures for that particular month only. For example, machine (1339) has an alarm B for 24hrs in Feb and 96hrs in Mar. How can I capture that?
Thanks heaps in advance.
Hi,
Please take following steps:
For your requirement 1:
1)Add an index column to original table first.
2)Create a new index column:
New Index =
VAR a =
CALCULATE (
MAX ( 'Table'[END_DT] ),
FILTER ( 'Table' , 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
)
var b = CALCULATE (
MAX ( 'Table'[START_DT] ),
FILTER ( 'Table' , 'Table'[Index] = EARLIER( 'Table'[Index] ) + 1 )
)
VAR check =
IF (
( 'Table'[START_DT] = a||'Table'[END_DT]=b)
,
CALCULATE(MIN ('Table'[Index]),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Machine ID] =EARLIER('Table'[Machine ID])
&& 'Table'[Alarm Type] =EARLIER('Table'[Alarm Type])
)
),
'Table'[Index]
)
RETURN
check
3)The result shows:
For your requirement 2:
1)Create a Month column and a new group column:
Month = MONTH('Table'[START_DT])
New Group =
IF (
MONTH ( 'Table'[START_DT] ) <> MONTH ( 'Table'[END_DT] ),
'Table'[Machine ID] & "-" & 'Table'[Alarm Type] & "-1",
'Table'[Machine ID] & "-" & 'Table'[Alarm Type] & "-2"
)
2)The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Hi
Thanks for the reply.
I have years worth of data so ther is not enough memory to create calculated columns.
Is there any chance that we can create measures?
=====
And the logic should be like this?
IF
machineID = earlier machineID AND
Start date = earlier End date AND
Alarm type = earlier Alarm type
Then, those multiple rows should combine as one.
Because on the same day, the same alarm type could appear multiple times with differnt timestamps.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Thank you for the reply, however, there is still an issue.
Not only do I need the total downtime, I also need to calculate alarm quantity as well.
What if machine 1339 and 5412 have alarms B like this:
Machine ID | Alarm Type | START_DT | END_DT | Downtime |
456 | F | 2/03/2020 0:00 | 2/03/2020 3:56 | 3.95 |
456 | W | 2/03/2020 0:00 | 2/03/2020 3:56 | 3.95 |
774 | F | 2/03/2020 5:27 | 2/03/2020 6:30 | 1.06 |
774 | W | 2/03/2020 5:27 | 2/03/2020 6:30 | 1.06 |
1339 | B | 29/02/2020 0:00 | 1/03/2020 0:00 | 24.00 |
1339 | B | 1/03/2020 0:00 | 2/03/2020 0:00 | 24.00 |
1339 | B | 2/03/2020 0:00 | 3/03/2020 0:00 | 24.00 |
1339 | B | 3/03/2020 0:00 | 4/03/2020 0:00 | 24.00 |
1339 | B | 4/03/2020 0:00 | 5/03/2020 0:00 | 24.00 |
1339 | B | 5/03/2020 8:00 | 5/03/2020 10:00 | 2.00 |
1339 | B | 5/03/2020 19:00 | 6/03/2020 2:00 | 7.00 |
3288 | F | 29/02/2020 7:45 | 29/02/2020 12:31 | 4.77 |
3479 | B | 29/02/2020 0:00 | 29/02/2020 13:40 | 13.67 |
4735 | F | 29/02/2020 8:00 | 29/02/2020 18:44 | 10.72 |
5412 | B | 29/02/2020 0:00 | 1/03/2020 0:00 | 24.00 |
5412 | B | 1/03/2020 0:00 | 2/03/2020 0:00 | 24.00 |
5412 | B | 2/03/2020 0:00 | 3/03/2020 0:00 | 24.00 |
5412 | B | 8/03/2020 0:00 | 9/03/2020 0:00 | 24.00 |
5412 | B | 9/03/2020 0:00 | 10/03/2020 0:00 | 24.00 |
5412 | B | 10/03/2020 0:00 | 10/03/2020 10:55 | 10.91 |
5412 | B | 10/03/2020 10:58 | 10/03/2020 11:58 | 1.00 |
5412 | F | 10/03/2020 10:55 | 10/03/2020 11:03 | 0.14 |
5851 | B | 29/02/2020 15:25 | 29/02/2020 19:51 | 4.44 |
6405 | B | 29/02/2020 0:00 | 29/02/2020 7:00 | 7.00 |
And the result should be like below. The total alarm would be 16 in this case. Notice the alarms for machine 5412 has reduced from 8 to 4.
Machine ID | Alarm Type | START_DT | END_DT | Downtime |
6405 | B | 29/02/2020 0:00 | 29/02/2020 7:00 | 7.00 |
1339 | B | 29/02/2020 0:00 | 5/03/2020 0:00 | 120 |
1339 | B | 5/03/2020 8:00 | 5/03/2020 10:00 | 2.00 |
1339 | B | 5/03/2020 19:00 | 6/03/2020 2:00 | 7.00 |
3479 | B | 29/02/2020 0:00 | 29/02/2020 13:40 | 13.67 |
5412 | B | 29/02/2020 0:00 | 3/03/2020 0:00 | 72.00 |
5412 | B | 8/03/2020 0:00 | 10/03/2020 10:55 | 58.91 |
5412 | B | 10/03/2020 10:58 | 10/03/2020 11:58 | 1.00 |
5412 | F | 10/03/2020 10:55 | 10/03/2020 11:03 | 0.14 |
3288 | F | 29/02/2020 7:45 | 29/02/2020 12:31 | 4.77 |
4735 | F | 29/02/2020 8:00 | 29/02/2020 18:44 | 10.72 |
5851 | B | 29/02/2020 15:25 | 29/02/2020 19:51 | 4.44 |
456 | F | 2/03/2020 0:00 | 2/03/2020 3:56 | 3.95 |
456 | W | 2/03/2020 0:00 | 2/03/2020 3:56 | 3.95 |
774 | F | 2/03/2020 5:27 | 2/03/2020 6:30 | 1.06 |
774 | W | 2/03/2020 5:27 | 2/03/2020 6:30 | 1.06 |
Hi,
I am not sure whom you are replying to but if it is me then i defeinitely do not understand your requirement.
This file should help you split
These measures should help you after the split to get data
Min(Table[Start Date])
Max(Table[EndDate])
Sum(Table[Downtime])
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |