Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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?

Need Help
Tips and Tricks
  • Chihiro's avatar
    Chihiro
    7 years ago

    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)

4 Replies

  • Chihiro's avatar
    Chihiro
    Icon for Solution Sage rankSolution Sage
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • Chihiro's avatar
        Chihiro
        Icon for Solution Sage rankSolution Sage
        7 years ago

        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)