DAX help check for duplicates

[CrazyHorse66]

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

[ERD]

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.