topic Re: Return correct status according to most recent entry. in DAX Commands and Tips

Return correct status according to most recent entry.

Anonymous — Thu, 31 Aug 2023 13:48:12 GMT

I have a summary table that I want to just see order numbers in this example "4507101" and next to it a status eg "Complete or Incomplete". For these work order numbers I have a number of entries as you can see below, I am only interested in the most recent one, as per the date/time in column 4.

The next issue is, if the most recent entry has the status "As Per Lead Engineer Jobsheet", I'm not interested in this. I want it to return the most recent "Complete" or "Incomplete".

I was thinking of MAX - 1 date, but I could have back to back "As Per Lead Engineer Jobsheet".

4507101	R Tuck	11/05/2023 08:28:00	11/05/2023 13:51:00	5.383333	As Per Lead Engineer Jobsheet
4507101	E Defew	11/05/2023 10:44:00	11/05/2023 13:37:00	2.883333	Complete
4507101	E Defew	11/05/2023 08:00:00	11/05/2023 10:44:00	2.733333	Incomplete
4507101	R Tuck	10/05/2023 08:26:00	10/05/2023 14:49:00	6.383333	As Per Lead Engineer Jobsheet
4507101	E Defew	10/05/2023 09:56:00	10/05/2023 14:44:00	4.8	Incomplete
4507101	J Dixon	10/05/2023 09:52:00	10/05/2023 12:02:00	2.166667	As Per Lead Engineer Jobsheet

So far I have the DAX:

Table2 = SUMMARIZE('Table1','Table1'[Work Order No],"Status", CALCULATE(MAX('Table1'[Status] ),FILTER('Table1','Aeromark Data'[Finish Time] = MAX('Table1'[Finish Time]))))

However this does not exclude "As Per Lead Engineer Jobsheet" status"

Re: Return correct status according to most recent entry.

Anonymous — Thu, 31 Aug 2023 14:17:59 GMT

I reposted this after cleaning it up, but I can't seem to see the new post and don't see the option to delete this one.

Re: Return correct status according to most recent entry.

ERD — Thu, 31 Aug 2023 15:18:29 GMT

Anonymous , you can try this measure:

Measure = VAR dt = CALCULATE ( MAX ( 'Table'[Finish Time] ), 'Table'[status] <> "As Per Lead Engineer Jobsheet", ALL ( 'Table'[Finish Time] ) ) VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt ) RETURN last_status

Re: Return correct status according to most recent entry.

Anonymous — Fri, 01 Sep 2023 07:38:59 GMT

Hi,

This works in terms of getting rid of "As Per Lead Engineer Jobsheet" thank you.

However, it does not eliminate duplicates. So if I have two "Incomplete" for the same order number on different dates, it returns both results. I'm only interested in the most recent incomplete.

Re: Return correct status according to most recent entry.

ERD — Fri, 01 Sep 2023 10:19:29 GMT

Hi, I don't know what's your resulting visual, you can try this:

Measure = VAR dt = CALCULATE ( MAX ( 'Table'[Finish Time] ), 'Table'[status] <> "As Per Lead Engineer Jobsheet", ALL('Table') ) VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt ) RETURN IF ( MAX ( 'Table'[Finish Time] ) = dt, last_status )

Re: Return correct status according to most recent entry.

Anonymous — Fri, 01 Sep 2023 13:45:33 GMT

Should this code be embedded within a 'Summarise'? As now when I try in a new column, I'm only returning a complete or blank status.

Re: Return correct status according to most recent entry.

Anonymous — Fri, 01 Sep 2023 14:00:20 GMT

Re: Return correct status according to most recent entry.

ERD — Fri, 01 Sep 2023 15:04:00 GMT

Anonymous , the code I've provided is for a measure, not a calculated column.

Slight adjustment for the measure:

Measure = VAR dt = CALCULATE ( MAX ( 'Table'[Finish Time] ), 'Table'[status] <> "As Per Lead Engineer Jobsheet", ALL('Table'[Finish Time]) ) VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt ) RETURN IF ( MAX ( 'Table'[Finish Time] ) = dt, last_status )

Calculated column:

Column = var ord_num = 'Table'[order number] VAR dt = CALCULATE ( MAX ( 'Table'[Finish Time] ), 'Table'[status] <> "As Per Lead Engineer Jobsheet", 'Table'[order number] = ord_num, ALL('Table') ) VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt ) RETURN last_status

Re: Return correct status according to most recent entry.

Anonymous — Tue, 05 Sep 2023 09:12:21 GMT

Hi ERD this is very close! However on some instances it does not work. I have noted one as an example below.

These two are the same work order no, so under 'StatusReal' I should not have two blanks, but one returning the correct status.

Re: Return correct status according to most recent entry.

ERD — Tue, 05 Sep 2023 10:17:18 GMT

Anonymous , without having the data, I am not able to reproduce the issue. Try to reproduce it in the demo table adding some values..

Re: Return correct status according to most recent entry.

Anonymous — Tue, 05 Sep 2023 12:43:43 GMT

This is the data. For some of these your solution works, for others not so well.

Date/Time	Work Order No	Resource Name	Start Time	Finish Time	Time on Site - Decimal	Status	Start Travel	Finish Travel	Travel Time - Decimal
30/08/2023 00:00:00	4651678	M Whincop	30/08/2023 12:08:00	30/08/2023 14:32:00	2.4	As Per Lead Engineer Jobsheet	30-08-23 09:40	30-08-23 12:08	2.4667
30/08/2023 14:32:00	4651678	S Irving	30/08/2023 12:09:00	30/08/2023 14:32:00	2.383333	Complete	30-08-23 09:45	30-08-23 12:09	2.4
21/08/2023 13:17:00	4633479	NMitchell	21/08/2023 09:17:00	21/08/2023 13:17:00	4	Incomplete	null	null	null
21/08/2023 00:00:00	4633479	N Phillips	21/08/2023 08:16:00	21/08/2023 09:31:00	1.25	As Per Lead Engineer Jobsheet	null	null	null
24/05/2023 00:00:00	4507102	W Trustrum	24/05/2023 00:00:00	24/05/2023 15:50:00	15.8333333333	As Per Lead Engineer Jobsheet	null	null	null
24/05/2023 13:37:00	4507102	B Jones	24/05/2023 13:22:00	24/05/2023 13:37:00	0.25	Complete	24-05-23 11:08	24-05-23 13:22	2.2333
16/05/2023 00:00:00	4507099	G Swann	16/05/2023 08:11:00	16/05/2023 17:32:00	9.35	As Per Lead Engineer Jobsheet	null	null	null
16/05/2023 16:57:00	4507099	M Barnard	16/05/2023 15:38:00	16/05/2023 16:57:00	1.316667	Complete	null	null	null

Re: Return correct status according to most recent entry.

ERD — Tue, 05 Sep 2023 15:50:21 GMT

Well, the problem here is due to the same finish time.

Column = var ord_num = 'Table'[Work Order No] VAR dt = CALCULATE ( MAX ( 'Table'[Finish Time] ), 'Table'[status] <> "As Per Lead Engineer Jobsheet", 'Table'[Work Order No] = ord_num, ALL('Table') ) VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt, 'Table'[status] <> "As Per Lead Engineer Jobsheet" ) RETURN last_status

Re: Return correct status according to most recent entry.

Anonymous — Tue, 05 Sep 2023 16:59:18 GMT

Yeah that I understand. I've found another instance though.

Here, I only have the statuses "complete" "incomplete". I should be returning the most recent by finish time status.

This turns up a blank.

Re: Return correct status according to most recent entry.

ERD — Tue, 05 Sep 2023 17:43:37 GMT

Demo data, please, or the file. Unfortunately, I cannot guess all the particularities of the data you have.

Or, you can also try this option:

Column = var ord_num = 'Table'[Work Order No] VAR dt = CALCULATE ( MAX ( 'Table'[Finish Time] ), 'Table'[status] <> "As Per Lead Engineer Jobsheet", 'Table'[Work Order No] = ord_num, ALL('Table') ) RETURN IF( 'Table'[Finish Time] = dt && 'Table'[status] <> "As Per Lead Engineer Jobsheet", 'Table'[Status])

Re: Return correct status according to most recent entry.

shak1701 — Wed, 06 Sep 2023 08:59:50 GMT

https://docs.google.com/spreadsheets/d/18NcrXxbjP4wgUbzj4W1D1gqfNn4dK9YZ/edit?usp=drive_link&ouid=108646412166187200606&rtpof=true&sd=true

Here you go. I've had to switch to my personal PC to share the link. This is the data I'm working with.

Thank you.

Re: Return correct status according to most recent entry.

ERD — Wed, 06 Sep 2023 09:20:04 GMT

The access is restricted.

Re: Return correct status according to most recent entry.

shak1701 — Wed, 06 Sep 2023 09:23:24 GMT

This should work now.

https://docs.google.com/spreadsheets/d/18NcrXxbjP4wgUbzj4W1D1gqfNn4dK9YZ/edit?usp=drive_link&ouid=108646412166187200606&rtpof=true&sd=true

Re: Return correct status according to most recent entry.

ERD — Wed, 06 Sep 2023 10:07:09 GMT

I do see a value:

Tested with the latest code option:

Column = var ord_num = 'Table'[Work Order No] VAR dt = CALCULATE ( MAX ( 'Table'[Finish Time] ), 'Table'[status] <> "As Per Lead Engineer Jobsheet", 'Table'[Work Order No] = ord_num, ALL('Table') ) RETURN IF( 'Table'[Finish Time] = dt && 'Table'[status] <> "As Per Lead Engineer Jobsheet", 'Table'[Status])

Re: Return correct status according to most recent entry.

Anonymous — Wed, 06 Sep 2023 10:30:53 GMT

ERD I think we're onto a winner. Thank you very much!!!