topic Re: Find Latest duplicate in DAX Commands and Tips

Find Latest duplicate

Anonymous — Fri, 08 Sep 2023 15:41:29 GMT

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

Re: Find Latest duplicate

some_bih — Sun, 10 Sep 2023 09:29:08 GMT

Hi Anonymous please provide more clear requirements:

you want to see

per employee ID

the latest date

for column Completed Date only

Re: Find Latest duplicate

Anonymous — Sun, 10 Sep 2023 18:22:33 GMT

That is correct some_bih

Re: Find Latest duplicate

some_bih — Sun, 10 Sep 2023 19:50:50 GMT

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.

Re: Find Latest duplicate

Anonymous — Mon, 11 Sep 2023 14:06:40 GMT

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

Re: Find Latest duplicate

some_bih — Mon, 11 Sep 2023 14:12:28 GMT

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

Re: Find Latest duplicate

Anonymous — Mon, 11 Sep 2023 14:16:31 GMT

Thank you

I am now getting all courses but still see duplicates

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

Re: Find Latest duplicate

some_bih — Mon, 11 Sep 2023 14:30:58 GMT

Hi Anonymous provide expected result for input

Re: Find Latest duplicate

Anonymous — Mon, 11 Sep 2023 14:52:54 GMT

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

Re: Find Latest duplicate

ERD — Mon, 11 Sep 2023 18:41:48 GMT

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

Re: Find Latest duplicate

Anonymous — Mon, 11 Sep 2023 20:44:35 GMT

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