Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need DAX Help: Measure of Consecutive Hours of Waste by Machine

Hey all,

 

I have a large set of production data and I am trying to create a measure of how many times we are seeing a particular scrap cause for 3 or more consecutive hours on the same machine. I am effectively trying to determine if we have an issue with troubleshooting response time. Unfortunately I am a novice when it comes to DAX and have no idea what that measure would look like. Below is a sample of what my data will look like. Should the measure work properly, it should show a count of 2, 2, & 2 for WC1, WC2, & WC3 on M1 and 0, 2, 1 on M2. Any help figuring out this syntax would be appreciated!

 

MachineDateHourWaste Cause
M16/25/20190WC2
M16/25/20190WC1
M26/25/20190WC1
M16/25/20191WC2
M16/25/20192WC2
M16/25/20193WC2
M16/25/20194WC3
M26/25/20195WC3
M26/25/20196WC3
M16/25/20197WC1
M16/25/20198WC1
M16/25/20199WC1
M26/25/20199WC2
M26/25/201910WC2
M26/25/201911WC2
M16/25/201912WC3
M16/25/201913WC3
M16/25/201914WC3
M16/25/201915WC3
M16/25/201916WC3
M16/25/201917WC1
M26/25/201918WC1
M16/25/201919WC2
M16/25/201920WC2
M16/25/201921WC2
M16/25/201922WC3
M26/25/201922WC3
M26/25/201923WC3
M16/25/201924WC1
M16/26/20190WC1
M16/26/20191WC1
M26/26/20192WC2
M26/26/20193WC2
M26/26/20194WC2
M16/26/20195WC3
M16/26/20196WC3
M16/26/20197WC3
M16/26/20198WC3
M16/26/20199WC3
M16/26/201910WC3
M26/26/201910WC3
M26/26/201911WC3
M26/26/201912WC3
12 REPLIES 12
Anonymous
Not applicable

Hi @Anonymous ,

 

Would something like this work?

 

 

Create a matrix with machines in the rows and the error codes in the columns. Then drag the hours to the values field. This should sum things up the way you desire. All of this could be done without DAX unless there's something you need to calculate.

Anonymous
Not applicable

Hey @Anonymous ,

 

Thanks for the reply, but no, I don't think that's quite what I'm looking for. I need a count of occurences where we see the same waste cause on the same machine for 3 or more consecutive hours. The Hour column in my data is representative of military time (0 = midnight, 13 = 1:00 PM, etc.). I believe what you are describing would be a sum of the hour values each machine showed a particular waste code, which wouldn't make any sense. I could perhaps change it to a count, which would give me the total # of hours we've seen a waste code, but that's still not quite what I'm looking for.

Anonymous
Not applicable

ah ok, so in that case:

 

 3houroccurence= if(DateDiff([starttime],[endtime],Hours)>=3,1,0)

 This should count every occurence that lasts three hours or more and then you can create a table or matrix to break it out by machine and error code. I'm not exactly sure how the date/time field is laid out. IF you have aclean/scrubbed pbix, I'd be happy to take a look.

Anonymous
Not applicable

Sorry for the late reply @Anonymous , got caught up in other projects.

 

Unfortunately the data I am pulling is being queried from a database, so I don't have a great way of scrubbing it. I've tried using the formula you posted (exactly as follows)

 

3houroccurence = if(DateDiff(Min(Waste[Date & Time]),Max(Waste[Date & Time]),HOUR)>=3,1,0)
 
This is much closer to what I am looking for, however still not quite it. It seems like what this is giving me is a value of 1 if there are ANY instances of a particular scrap code being seen on a particular machine for more than three hours. What I need is a count of how many times that is occuring (+1 for every instance). To make things a little easier I have concatenated my Date & Hour fields into a single Date & Time column (referenced in the formula), which is formatted like "6/27/2019 2:00:00 PM".
 
Thanks again for the help!
Anonymous
Not applicable

Ah I see, so for example. we have a machine error out at 1and it doesnt stop till 6 hypothetically. So it counts 1 to 4, 1 to 5, and 1 to 6 even though this is one instance?

 

If this is a case we need a way to define the start and stop time of each instance. Then we can measure the time difference between each instance to determine if it is 3 hours or longer.

Anonymous
Not applicable

Finding a way to define the start and stop time of each instance is exactly what I'm after.


@Anonymous wrote:

... Then we can measure the time difference between each instance to determine if it is 3 hours or longer.


Just to clarify, the time difference you are referring to here is the difference between the start of the machine error being seen and the last consecutive hour it is seen, correct?

 


@Anonymous wrote:

Ah I see, so for example. we have a machine error out at 1and it doesnt stop till 6 hypothetically. So it counts 1 to 4, 1 to 5, and 1 to 6 even though this is one instance?


Not quite what I'm getting at. You are correct that, if a machine errors out from 1-6, it should count as one instance. To continue with this example, say the same machine errors out again for the same error from 8-11, my count should now read '2' for that particular error on that particular machine. What the measure is currently giving me is a value of '1' so long as there is an instance of 3 or more hours, regardless of how many instances there are. It is currently determining IF there is at least one three hour instance, rather than how many. Hope that makes sense.

Anonymous
Not applicable

Ok I understand now, sorry for the confusion, I think this may come down to data structure. 

 

"Just to clarify, the time difference you are referring to here is the difference between the start of the machine error being seen and the last consecutive hour it is seen, correct?"

 

Right, here's how I envision the data:

solution.png

 

 

 

With the data laid out like this we can create the column 3houroccurence using the measure we defined. Then we can sum that column to get the number of occurences.

Anonymous
Not applicable

No worries, these things tend to be a little difficult to communicate over text without context. Unfortunately my data does not contain an error start time and end time, otherwise this would be a lot easier. If an error is reported I get the date & hour it is reported, what the code is, and what machine it is on. If the error persists into the next hour an additional row is added to the data with the exact same info except with hour+1. The table I included in my original post is almost identical in format to my actual data, with the exception of the date & time field I mentioned I added earlier.

Anonymous
Not applicable

Hmm, so how would you tell the difference between:

 

Machine 1 throws an error at 1 and it isn't cleared until 10 (one error)
and

Machine 1 throws an error at 1 and it is cleared at 2. Then another error is thrown at 3 and persists until 6. Then another error is thrown at 7 until 10. (two errors)

 

With the way the data is structured now, there's no way to tell when each incident begins and ends, which would make knowing the duration very difficult.

Anonymous
Not applicable

Indeed, it is not a perfect solution, but the assumption is that if the error is truly resolved, then it will not reported within the next  consecutive hour. That being said, I think you're starting to get why I'm having such trouble with this haha.

 

It may help if I give you a bit of context for what this data represents IRL:

These "errors" are actually material scrap codes. There are certain causes of scrap we have that can be fixed by our operators making adjustments on their machines. If they make the proper adjustments (aka troubleshoot) to the machine, they should no longer be scrapping material and thus we should not continue to see the code reported. If the problem is considered resolved but is then seen again within the next hour, chances are their troubleshooting was ineffectual and the problem was not actually resolved. What I am trying to determine, is if this troubleshooting is being done promptly (< 3 hours), or if we have issues with our response time.

Anonymous
Not applicable

Yes, it's a pretty challenging situation, because with the current data structure there's no way for power bi to determine when something begins or ends. It will roll the measure forward for each row without checking to see if it's part of an existing error period. I'm not really sure I see a way to do this with the current level of information the database is giving you as an output, sorry. I'll keep thinking about it and see if Ican come up with something.

Anonymous
Not applicable

Yeah, I feel like there has to be some string of IF/OR/AND that would work, but like I said, I'm a DAX noob, so it's a little over my head. I appreciate you giving it a shot!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.