DAX help check for duplicates

CrazyHorse66 — Wed, 13 Oct 2021 10:11:07 GMT

Hi, thanks in advance for any help, we have the following requirements:

I've tried a few different methods to pull this together but all hit a memory issue, any suggestions on the best approach/script?

To identify duplicate clients based on email address:

1. For clients creating a new case in September, using email address how many have a different case same month

2. For clients creating a new case in September, using email address how many have a different case previous month(s)

Fields:

CaseSurrogateKey, Email, InteractionDate, FirstSessionYN

Check if the email address exists previously but with a different case

Example:

Bob flagged as a duplicate client (based on email) with a new case same month

Ted flagged as a duplicate client (based on email) with a new case different month(s) - as has a case in Jul and Aug already

Re: DAX help check for duplicates

ERD — Wed, 13 Oct 2021 16:22:07 GMT

Hi CrazyHorse66 ,

As a general recommendation, please, provide a sample data as text, use the table tool in the editing bar. This will increase your chances to get an answer.

As for your case, you can try these measures:

firstSession = VAR currentEmail = SELECTEDVALUE ( T[Email] ) VAR currentCase = SELECTEDVALUE ( T[Case] ) VAR currentDate = SELECTEDVALUE ( T[Date] ) VAR firstSession = MINX ( FILTER ( ALL ( T ), T[Case] = currentCase && T[Email] = currentEmail ), T[Date] ) RETURN IF ( currentDate = firstSession, "Y", "N" )

flag = VAR currentEmail = SELECTEDVALUE ( T[Email] ) VAR _t = ADDCOLUMNS ( CALCULATETABLE ( ALLSELECTED ( T ), T[Email] = currentEmail ), "@month", CALCULATE ( MONTH ( MAX ( T[Date] ) ) ) ) VAR _t2 = SUMMARIZE ( _t, [@month] ) RETURN SWITCH ( TRUE (), [firstSession] = "Y" && COUNTROWS ( _t2 ) = 1, "same month", [firstSession] = "Y" && COUNTROWS ( _t2 ) > 1, "prev month", BLANK () )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

topic DAX help check for duplicates in DAX Commands and Tips

DAX help check for duplicates

Re: DAX help check for duplicates