Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
rafterse
Helper I
Helper I

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

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 

UserLevel 1Level 2Clean up action
Bill L2-aL2-aDuplicated in L1-A
Bill L2-bL2-bDuplicated in L1-A
Bill L2-cL2-cDuplicated in L1-A
Bill L1-AL2-aNo Action
Bill L1-AL2-bNo Action
Bill L1-AL2-cNo Action
Bill L1-AL2-dNo Action
SueL2-eL2-eKeep
SueL2-fL2-fDuplicated in L1-B
SueL2-gL2-gDuplicated in L1-B
SueL2-hL2-hDuplicated in L1-B
SueL1-BL2-fNo Action
SueL1-BL2-gNo Action
SueL1-BL2-hNo Action
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1752721903593.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1752721903593.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Abhilash_P
Kudo Kingpin
Kudo Kingpin

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
Super User
Super User

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.

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.