Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Find Latest duplicate

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's avatar
    ERD
    2 years ago

    Hi, Anonymous , 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

     

10 Replies

  • ERD's avatar
    ERD
    Community Champion
    2 years ago

    Hi, Anonymous , 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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi Anonymous please provide more clear requirements: 

    you want to see

    per employee ID

    the latest date

    for column Completed Date only 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      That is correct some_bih 

      • some_bih's avatar
        some_bih
        Community Champion
        2 years ago

        Hi Anonymous 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.