Count of events that span several shifts

[MM159159]

I have a large dataset, in which each row of data represents if a peice of equipment is down, per shift (1 = Day 2 = Night)

Each row has a date (only date, no time; EDIT: I have since figured out how to calculate a starttime & finish time for the events), duration in hrs for downtime and other such data such as machine ID, a comment about specifics etc

Im trying to find a way to calcualte the total occurance of down codes that not only may occur several times in a single shift, but may also span several shifts.

Eg. If one peice of machinery went down on the 20th of Jan 2021 - stayed down until 25th of Jan 2021 - under the same code, then this is one event. However in the dataset it would have 10 entires, each row stipulating it was down for 12 hours.

However if another peice of machinery went down on the 20th of Jan, was fixed in an hour, and went down again, then this is 2 codes for the same day.

I have a peice of machinery that went down for several months, and it keeps returning 62 events per month.

I have no unique identifiers within the dataset that I could uniquely count up; Such as workorder numbers etc.

I appreciate any help anyone can give!

[MM159159]

@johnt75 - No such luck, in the dataset below that formula returns the number 3;

So in this example, There are 9 events, some of which - such as Waiting on Parts - started on the 5/3/22 16:11 and went to 8/3/22 15:58. But this is considered a single event. So that should receive a 1 - along with the likes of Ele faults, which only was in a down status for less than a minute.

To top it off, some events may finish at excactly a certain time say 2:00:04 then start again, at the exact same time. But would be considered only a single code in this instance too.

[V-lianl-msft]

Hi @MM159159 ,

Sorry,I'm not clear about your data model. Could you paste some sample data so that we can do more tests.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MM159159]

Sorry about that, Im doing some testing myself so have put together some sample data with every possible scenario.

Each peice of equipment (10, 12, 15, 25, 28 etc) has a condition each. they start at one time, and end at another, but each row is a shift. (1 is Dayshift 2 is Nightshift). 

Distinct count on code is not going to work either, as many machines may have the same code etc.

Table Copy:

So in this example, every equipment should have the count of 1 for frequency. If a code spans a a few hours, or a few weeks, its a count of 1.

Date	Shift	Equipment ID	StartTime	EndTime	Code
24/03/2022	1	RD10	06:00:00	15:00:00	11
24/03/2022	2	RD12	06:45:00	21:00:00	45
25/03/2022	1	RD15	08:30:00	18:00:00	52
25/03/2022	2	RD15	18:00:00	02:30:00	52
26/03/2022	1	RD25	08:30:00	18:00:00	64
26/03/2022	2	RD25	18:00:00	06:00:00	64
27/03/2022	1	RD25	06:00:00	10:00:00	64
27/03/2022	2	RD28	20:00:00	06:00:00	84
28/03/2022	1	RD28	06:00:00	10:00 AM	84
27/03/2022	2	RD30	20:00:00	06:00:00	122
28/03/2022	1	RD30	06:00:00	18:00:00	122
28/03/2022	2	RD30	18:00:00	23:30:00	122
27/03/2022	1	RD35	07:21:00	18:00:00	512
27/03/2022	2	RD35	18:00:00	06:00:00	512
28/03/2022	1	RD35	06:00:00	18:00:00	512
28/03/2022	2	RD35	18:00:00	06:00:00	512
29/03/2022	1	RD35	06:00:00	10:21:00	512
27/03/2022	1	RD42	11:24:00	18:00:00	674
27/03/2022	2	RD42	18:00:00	06:00:00	674
28/03/2022	1	RD42	06:00:00	18:00:00	674
28/03/2022	2	RD42	18:00:00	06:00:00	674
29/03/2022	1	RD42	06:00:00	18:00:00	674
29/03/2022	2	RD42	18:00:00	20:23:00	674
27/03/2022	2	RD50	20:00:00	06:00:00	958
28/03/2022	1	RD50	06:00:00	18:00:00	958
28/03/2022	2	RD50	18:00:00	06:00:00	958
29/03/2022	1	RD50	06:00:00	14:00:00	958
27/03/2022	2	RD92	20:00:00	06:00:00	1024
28/03/2022	1	RD92	06:00:00	18:00:00	1024
28/03/2022	2	RD92	18:00:00	06:00:00	1024
29/03/2022	1	RD92	06:00:00	18:00:00	1024
29/03/2022	2	RD92	18:00:00	20:27:00	1024

[johnt75]

You could try creating a measure like 

Total down codes = COUNTROWS( SUMMARIZE( 'Table', 'Table'[Machine ID], 'Table'[Down code]))