Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 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...
  • ERD's avatar
    ERD
    3 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

     

some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
3 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
3 years ago

That is correct some_bih 

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        some_bih
        Icon for Community Champion rankCommunity Champion
        3 years ago

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