Pulling info from event based on highest time in each category

[123CaM321]

So I have data similar to below

Events occur, and time for event is calculated based on timestamp. The event gets a category (1 of 3 choices)

I have a dashboard that slices by year,month,week and shows data based on the events within that timeframe. What I need to do is find the longest event for each category based on my chosen period on the dashboard. I've been stuck on this for days and just can't work it out.

Creating a matrix with category in rows and using a measure to calculate MAX of event time will give me the top of each category however I've then got no other info on the event, only the time

Really hope someone can help. Thanks!

[123CaM321]

An update on how I've achieved this in the meantime. I see this as a dirty fix as there is the potential (even though highly unlikely) for a duplicate time to occur.

 - Filter the table by one of the categorys and find max time in breakdown. Save variable

 - Use variable to filter the table again, this time using the number found (ideally occurs once so ends up with a one row table

- concatenate the info I require from this returned row

- return result

[123CaM321]

Hi Ashish.

Expected results from this data would be

Technological - 74 - Overcap_reclose

Organisational - 756 - Checkweigher_2

Technical - 128 - Sleever

TIME	DOWNTIME	MACHINE	COMMENT	ACTION	SUB CATEGORY	CATEGORY
24/10/2022 18:51	756	Checkweigher_2	Line down for PRE FSI audit cleaning	This should be blocked out as planned downtime?	Operational	Organizational
26/10/2022 11:10	270	Filler			Operational	Organizational
16/10/2022 8:40	128	Sleever	Excessive rejects at sleever	Replacement of gear box for right side belt	Technical	Technical
17/10/2022 9:34	83	Clincher	HVAC - on Chiller affected the line		Technical	Technical
28/10/2022 7:27	80	Shared Wrapper			Operational	Organizational
18/10/2022 21:31	74	Overcap_Reclose	jamming at sorter		Material Defects	Technological
18/10/2022 20:20	71	Sleever	Fault 350- Wrong Cutting Position. Occurred after sleeve roll change over. Checked Keyence Sensor was responding  but had error on auto-run. Found the replaced sleeve roll had very light printed sensing mark.	Isolated that sleeve roll then Performed Keyence Calibration procedure in reference with good sleeve mark	Material Defects	Technological
24/10/2022 3:10	68	Seamer	Scratches and dents due to seamer timing		Technical	Technical
25/10/2022 12:35	65	Checkweigher_2	rolex wouldn't start on SCADA.	Sina and cameron called to resolve the problem	Technical	Technical
23/10/2022 12:34	65	Overcap_Reclose	kando rubber belt worn needs changing	Called Engineer to fix.	Technical	Technical
11/10/2022 9:44	64	Overcap_Reclose	Cylinder Snapped on the guide	Called Engineer to replace the Cylinder	Technical	Technical
26/10/2022 2:40	63	Case Packer	size change over and Aggregation issues.	Jesh and Don.	Operational	Organizational
11/10/2022 22:55	61	Checkweigher_1	Initially tipdan screen was not connecting to T drive and missing PO from the list , after basic troubleshooting , changed the screen to a new one and the label was not printing. Swapped Tree line tipdan screen and label , replaced files with Mtn line dispensing to fix the issue		Technical	Technical

[Dinesh_Suranga]

@123CaM321 ,

Hi,

1  Add CATEGORY and DOWNTIME to table

2  Right click on DOWNTIME and click on Maximum.

3  Create a mesure using following DAX formula.

Machine Name = CALCULATE( Max('Table'[MACHINE] ) , 'Table'[DOWNTIME] =  MAX('Table'[DOWNTIME]))

4  Add new created mesure to table.

Without seen the data model it is hard to give a best solution.

But I hope this will help you to solve your issue.

If you have any question regarding this solution please reply to this message.

Please acept this as solution if it helped you to solve the issue.

Thank you.

[Ashish_Mathur]

Hi,

Share some data that can be pasted in an MS Excel file and show the expected result.