Find Latest duplicate

[briansull]

Hi All,

I have a Scorm learning dataset that has many duplicate records. This happens if a user starts a course and does not finish but comes back and finishes at a later date. The system creates a record for every attempt 😞 

I need to filter and show the latest attempt with result 

i.e.

Course ID |   Complete | Employee ID | Completed Date

1               |         0         |      111          |  09-12-22

1               |          1        |       111         |  12-12-22

[ERD · Accepted solution]

Hi, @briansull , one of the options for your measure:

status =
VAR course = MAX ( Table1[Course] )
VAR employee = MAX ( Table1[Employee] )
VAR dt =
    CALCULATE (
        MAX ( Table1[Date] ),
        Table1[Employee] = employee,
        Table1[Course] = course,
        ALL ( Table1[Date] )
    )
VAR res =
    IF (
        MAX ( Table1[Date] ) = dt,
        CALCULATE (
            MAX ( Table1[Completed] ),
            Table1[Employee] = employee,
            Table1[Course] = course
        )
    )
RETURN
    res

[briansull]

I think that's it thank so much. I need to verify still but looks good

[some_bih]

Hi @briansull please provide more clear requirements: 

you want to see

per employee ID

the latest date

for column Completed Date only 

[briansull]

That is correct some_bih 

[some_bih]

Hi @briansull please find following measure and outuput

MaxDate Compplete Per Employee =

VAR __max_date_complete =

MAX ( Sheet1[Completed Date] )

VAR __employee_ID =

SELECTEDVALUE ( Sheet1[Employee ID] )

VAR __course_ID =

SELECTEDVALUE ( Sheet1[Course ID] ) -- if more course is choosed

VAR __Result =

CALCULATE (

    MAX ( Sheet1[Completed Date] ),

    Sheet1[Completed Date] = __max_date_complete

        && Sheet1[Employee ID] = __employee_ID

        && Sheet1[Course ID] = __course_ID,

    Sheet1[Complete] = "1" -- 2 = completed, 0 not completed

)

RETURN __Result

Did I answer correctly? Kudos appreciate / accept solution.

[briansull]

Thank you Some_bih,

The measure is nearly correct but i need to list all courses with the one measure if completed or not completed. The proposed solution measure only brings back completed if set to 1

[some_bih]

Hi @briansull check v2 below😊

Did I answer your question? Kudos appreciated / accept solution!

MaxDate Compplete Per Employee v2 =

VAR __max_date_complete =

MAX ( Sheet1[Completed Date] )

VAR __employee_ID =

SELECTEDVALUE ( Sheet1[Employee ID] )

VAR __course_ID =

SELECTEDVALUE ( Sheet1[Course ID] ) -- if more course is choosed

VAR __Result =

CALCULATE (

    MAX ( Sheet1[Completed Date] ),

    Sheet1[Completed Date] = __max_date_complete

        && Sheet1[Employee ID] = __employee_ID

        && Sheet1[Course ID] = __course_ID

)

RETURN __Result

[briansull]

Thank you

I am now getting all courses but still see duplicates

i.e. course 1 is complete, course 1 is not complete

[some_bih]

Hi @briansull provide expected result for input

[briansull]

Thank you. I need to show who hasn't completed courses either

i.e.

Table

Course 1 | Employee 2 | Completed 1 | Date 20-12-2022

Course 1 | Employee 2 | Completed 0  | Date 19-12-2022

Course 2 | Employee 2 | Completed 0 | Date 21-12-2002

Expected Results

Course 1 | Employee 2 | Completed 1 | Date 20-12-2022

Course 2 | Employee 2 | Completed 0 | Date 21-12-2002