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

     

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

        Thank you

         

        I am now getting all courses but still see duplicates

         

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