cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tureka Frequent Visitor
Frequent Visitor

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

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

Hi @tureka ,

 

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.

tureka Frequent Visitor
Frequent Visitor

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

Hey @calebmfoster ,

 

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.

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

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.

tureka Frequent Visitor
Frequent Visitor

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

Sorry for the late reply @calebmfoster , 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!

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

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.

tureka Frequent Visitor
Frequent Visitor

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

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


@calebmfoster 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?

 


@calebmfoster 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.

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

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.

tureka Frequent Visitor
Frequent Visitor

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

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.

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

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)