Forum Discussion

danialsj's avatar
danialsj
Frequent Visitor
6 years ago
Solved

Remove rows based on a criteria

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!

 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

     

3 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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.

     

  • JosefPrakljacic's avatar
    JosefPrakljacic
    Solution Sage
    6 years ago

    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

  • danextian's avatar
    danextian
    Super User
    6 years ago

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