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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Shak1254
Helper I
Helper I

Return correct status according to most recent entry.

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". 

4507101R Tuck11/05/2023 08:28:0011/05/2023 13:51:005.383333As Per Lead Engineer Jobsheet
4507101E Defew11/05/2023 10:44:0011/05/2023 13:37:002.883333Complete
4507101E Defew11/05/2023 08:00:0011/05/2023 10:44:002.733333Incomplete
4507101R Tuck10/05/2023 08:26:0010/05/2023 14:49:006.383333As Per Lead Engineer Jobsheet
4507101E Defew10/05/2023 09:56:0010/05/2023 14:44:004.8Incomplete
4507101J Dixon10/05/2023 09:52:0010/05/2023 12:02:002.166667As 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"

 

1 ACCEPTED SOLUTION

I do see a value:

ERD_0-1693994784215.png

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])

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

18 REPLIES 18
ERD
Super User
Super User

@Shak1254 , 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

ERD_0-1693495102568.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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. 

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 )

ERD_0-1693563562551.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

My resultMy result

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. 

@Shak1254 , 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 )

ERD_0-1693580049148.png

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

ERD_1-1693580633717.png

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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

 

Screenshot 2023-09-05 101110.pngScreenshot 2023-09-05 101343.png

 

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

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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

 

Date/TimeWork Order NoResource NameStart TimeFinish TimeTime on Site - DecimalStatusStart TravelFinish TravelTravel Time - Decimal
30/08/2023 00:00:004651678M Whincop30/08/2023 12:08:0030/08/2023 14:32:002.4As Per Lead Engineer Jobsheet30-08-23 09:4030-08-23 12:082.4667
30/08/2023 14:32:004651678S Irving30/08/2023 12:09:0030/08/2023 14:32:002.383333Complete30-08-23 09:4530-08-23 12:092.4
21/08/2023 13:17:004633479NMitchell21/08/2023 09:17:0021/08/2023 13:17:004Incompletenullnullnull
21/08/2023 00:00:004633479N Phillips21/08/2023 08:16:0021/08/2023 09:31:001.25As Per Lead Engineer Jobsheetnullnullnull
24/05/2023 00:00:004507102W Trustrum24/05/2023 00:00:0024/05/2023 15:50:0015.8333333333As Per Lead Engineer Jobsheetnullnullnull
24/05/2023 13:37:004507102B Jones24/05/2023 13:22:0024/05/2023 13:37:000.25Complete24-05-23 11:0824-05-23 13:222.2333
16/05/2023 00:00:004507099G Swann16/05/2023 08:11:0016/05/2023 17:32:009.35As Per Lead Engineer Jobsheetnullnullnull
16/05/2023 16:57:004507099M Barnard16/05/2023 15:38:0016/05/2023 16:57:001.316667Completenullnullnull
          

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

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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. 

 

Screenshot 2023-09-05 175851.png

 

This turns up a blank. 

I do see a value:

ERD_0-1693994784215.png

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])

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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

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])

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

https://docs.google.com/spreadsheets/d/18NcrXxbjP4wgUbzj4W1D1gqfNn4dK9YZ/edit?usp=drive_link&ouid=10...

 

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. 

The access is restricted.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Shak1254
Helper I
Helper I

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. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors