Remove duplicate rows based on max value of a different column

[Anonymous]

ID	Amount	Receivable
134	392.66	0
134	392.66	392.66
134	8236.88	8236.88
152	2492.09	0
152	2532.16	2532.16
152	12628.74	0
152	12628.74	12628.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?

[Chihiro]

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]

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): 

ID	Amount	Receivable
134	392.66	0
134	392.66	392.66
134	8236.88	8236.88
152	2492.09	0
152	2532.16	2532.16
152	12628.74	0
152	12628.74	12628.74
152	12628.74	9628.74

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

[Chihiro · 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)

[Anonymous]

PERFECT! This worked. Thanks!