Remove rows based on a criteria

[danialsj]

My data looks like the following:

ID          Charge_Code           Amount

1001      Opening                  1100

1001      Opening                  110
1001      Purchase                  500

1002      Opening                  2000
1002      Purchase                  700

1003      Opening                  1500

1004      Opening                  170

1004      Opening                  1700

Some of my data has been incorrectly entered. For example IDs 1001 and 1004 have two account opening entries when they should only have one. This is because someone incorrectly entered the first value (highlighted in red) and then entered the second value correctly.

I want my data to look like the following:

ID          Charge_Code           Amount

1001      Opening                  110
1001      Purchase                  500

1002      Opening                  2000
1002      Purchase                  700

1003      Opening                  1500

1004      Opening                  1700

The above table has deleted the IDs with duplicate 'Opening' Charge_Code and kept the latest entry. My data does not have a time stamp but the order of the rows in default is chronological. 

Please help!

[Icey · Accepted solution]

Hi @danialsj ,

Assume that the second duplicate value is correct.

Try this:

Measure 2 =
VAR Duplicate =
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Charge_Code] )
        ) <> 1,
        1,
        0
    )
VAR index_1 =
    MAX ( 'Table'[Index] ) + 1
VAR Measure_ =
    CALCULATE (
        DISTINCT ( 'Table'[Amount] ),
        index_1 = 'Table'[Index],
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Charge_Code] )
    )
RETURN
    IF ( Duplicate = 0, MAX ( 'Table'[Amount] ), Measure_ )

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @danialsj Does your table have some kind of a timestamp to determine which among the row by ID is the latest? Supposing you have it or any other indicator columns, your DAX column formula should be something like this:

Latest =
VAR __LATEST =
    CALCULATE ( MAX ( 'Table'[Timestamp] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    IF (
        'Table'[Chage_Code] <> "Opening"
            || 'Table'[Timestamp] = __LATEST,
        "Yes",
        "No"
    )

[JosefPrakljacic]

Hey @danialsj,

this article will show you how to get it done.

Filtering the Top 1 Entry for each charge code in Power BI 😉 

If this post was helpful may I ask you to mark it as solution and give it a 'thumbs up'? This will also help others

Have a nice day!

BR,
Josef
Graz - Austria