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 August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Remove duplicate rows based on max value of a different column

IDAmountReceivable
134392.660
134392.66392.66
1348236.888236.88
1522492.090
1522532.162532.16
15212628.740
15212628.7412628.74

 

I am having issues eliminating the rows in Red. I would love to get rid of rows where Amount is the same per ID and Receivable is 0 (like the rows in red). Any help please?

1 ACCEPTED SOLUTION

Then add calculated column to table.

Something like...

Flag =
IF (
    OR (
        Table1[Receivable] <> 0,
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                [ID] = EARLIER ( Table1[ID] )
                    && [Amount] = EARLIER ( Table1[Amount] )
            )
        )
            = 1
    ),
    1,
    0
)

Then... you can use [Flag]<>0 in your measures as part of filter context... or create new table using.

NewTable = FILTER((Table1,Table1[Flag]<>0)

View solution in original post

4 REPLIES 4
Chihiro
Solution Sage
Solution Sage

If using DAX, you could create new table using following.

Table =
SUMMARIZE (
    Table1,
    Table1[ID],
    Table1[Amount],
    "Recievable", MAX ( Table1[Receivable] )
)

If in Query Editor using "M"... you can transform the table itself by...

Select "ID" & "Amount" columns. Right click -> Group by. Name Aggregate column "Recievable" and Max of "Receivable column.

= Table.Group(#"Changed Type", {"ID", "Amount"}, {{"Recievable", each List.Max([Receivable]), type number}})

 

Anonymous
Not applicable

Thanks alot. But with this approach, this will only work if Receivable is 0 in all cases of duplicate Amount per ID. If at any point, there is a record like the last record here (it's a newly added row for the purpose of explaining what i mean): 

IDAmountReceivable
134392.660
134392.66392.66
1348236.888236.88
1522492.090
1522532.162532.16
15212628.740
15212628.7412628.74
15212628.749628.74

 
It will also be kicked out cos of the MAX aggregation. Meanwhile, I want records like that to remain

Then add calculated column to table.

Something like...

Flag =
IF (
    OR (
        Table1[Receivable] <> 0,
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                [ID] = EARLIER ( Table1[ID] )
                    && [Amount] = EARLIER ( Table1[Amount] )
            )
        )
            = 1
    ),
    1,
    0
)

Then... you can use [Flag]<>0 in your measures as part of filter context... or create new table using.

NewTable = FILTER((Table1,Table1[Flag]<>0)

Anonymous
Not applicable

PERFECT! This worked. Thanks!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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