Create a column in DAX to show the duplicates of another column by user

[rafterse]

All, i need to clean up some data

I need to remove the duplicates that show in column "Level 2" per user

this is the data 

i need the dax for "Clean up action" based on the data in the first 3 columns 

User	Level 1	Level 2	Clean up action
Bill	L2-a	L2-a	Duplicated in L1-A
Bill	L2-b	L2-b	Duplicated in L1-A
Bill	L2-c	L2-c	Duplicated in L1-A
Bill	L1-A	L2-a	No Action
Bill	L1-A	L2-b	No Action
Bill	L1-A	L2-c	No Action
Bill	L1-A	L2-d	No Action
Sue	L2-e	L2-e	Keep
Sue	L2-f	L2-f	Duplicated in L1-B
Sue	L2-g	L2-g	Duplicated in L1-B
Sue	L2-h	L2-h	Duplicated in L1-B
Sue	L1-B	L2-f	No Action
Sue	L1-B	L2-g	No Action
Sue	L1-B	L2-h	No Action

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(LEFT(Data[Level 1],2)="L1","No Action",if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[User]=EARLIER(Data[User])&&Data[Level 2]=EARLIER(Data[Level 2])))>1,"Remove","Keep"))

Hope this helps.

[Abhilash_P]

Clean up action = 
VAR Has_Capitalized_Level1 =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[User] = EARLIER('Table'[User]) &&
            'Table'[Level 1] IN {"A", "B"}
        )
    )
VAR Is_Capitalized_Row =
    'Table'[Level 1] IN {"A", "B"}
VAR Is_Duplicate_Level2 =
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[User] = EARLIER('Table'[User]) &&
            'Table'[Level 1] IN {"A", "B"} &&
            'Table'[Level 2] = EARLIER('Table'[Level 2])
        )
    )
RETURN
    IF(
        Is_Capitalized_Row,
        "No Action",
        IF(
            Has_Capitalized_Level1 > 0 && Is_Duplicate_Level2 > 0,
            "Duplicated in " & MAXX(
                FILTER(
                    'Table',
                    'Table'[User] = EARLIER('Table'[User]) &&
                    'Table'[Level 1] IN {"A", "B"} &&
                    'Table'[Level 2] = EARLIER('Table'[Level 2])
                ),
                'Table'[Level 1]
            ),
            "Keep"
        )
    )

Hi @rafterse ,
You can create a calculated column in DAX that will flag duplicates of Level 2 per user when the capitalized Level 1 (A or B) exists for that user.
Here is the DAX 

[pankajnamekar25]

Hello @rafterse 

Try this DAX

Clean up action =

VAR CurrentUser = 'Table'[User]

VAR CurrentLevel1 = 'Table'[Level 1]

VAR CurrentLevel2 = 'Table'[Level 2]

VAR CapitalMatch =

    CALCULATE (

        MAX ( 'Table'[Level 1] ),

        FILTER (

            'Table',

            'Table'[User] = CurrentUser

                && UPPER('Table'[Level 1]) = 'Table'[Level 1]

                && 'Table'[Level 2] = CurrentLevel2

        )

    )

RETURN

    IF (

        UPPER ( CurrentLevel1 ) = CurrentLevel1,

        "No Action",

        IF (

            NOT ISBLANK ( CapitalMatch ),

            "Duplicated in " & CapitalMatch,

            "Keep"

        )

    )

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.