How to identify a row based on another row?

[EmmaLiuLiu]

Hello,

I got stuck on this column creation for a while. Really hope someone could help me out here. I even start to doubt if what I want to achieve is doable in power bi or not....

What I want to achieve:

1. If it's a duplicate in column "key", the column "Quantity" has a positive value that has an offset negative value in another row, and the Plant codes are different. Then mark the negative quantity row "delete". Like the last two rows, I want to keep the last second one and delete the last one. So these two records indicate the same transaction. One receiving and one supplying. I only want to keep one.
2. If the movement type "351" is a duplicate in column “key”, the plant codes are the same, then mark "delete" to the row that has a Posting Date that’s no later than the one in the row with movement type “352” . So movement 352 has a corresponding movement 351, which needs to be identified.

It can be in two columns since these are two different requests there. 

@muhammad_786_1 I couldn't upload files but I copied and pasted my sample file here. You can copy and paste over and import into your power bi desktop? Sorry for all the trouble. Thanks in advance!!!

Sample table:

Plant	Material	Movement type	Purchase order	Item	key	Posting Date	Quantity
6417	0-810-030-805	351	4000121095	70	0-810-030-805400012109570	3/13/2024	8.00
6417	0-810-030-805	352	4000121095	70	0-810-030-805400012109570	3/13/2024	-1.00
6417	0-810-030-805	351	4000121095	70	0-810-030-805400012109570	8/8/2023	3.00
6417	0-810-034-517	351	4000121095	90	0-810-034-517400012109590	8/7/2023	1.00
6602	0-810-015-654	351	4000121902	10	0-810-015-654400012190210	9/5/2023	2.00
6602	0-810-015-654	351	4000121902	10	0-810-015-654400012190210	11/8/2023	3.00
6602	0-810-015-619	351	4000121902	100	0-810-015-6194000121902100	10/3/2023	2.00
6602	0-810-018-910	351	4000121902	210	0-810-018-9104000121902210	10/18/2023	4.00
6602	0-810-020-807	351	4000121902	220	0-810-020-8074000121902220	10/18/2023	3.00
6602	0-810-018-908	351	4000121902	230	0-810-018-9084000121902230	10/18/2023	12.00
6602	0-810-018-908	352	4000121902	230	0-810-018-9084000121902230	10/19/2023	-6.00
6602	0-810-018-908	351	4000121902	230	0-810-018-9084000121902230	11/2/2023	6.00
6482	0-810-037-471	351	4000122926	10	0-810-037-471400012292610	10/9/2023	1.00
6509	0-810-037-471	351	4000122926	10	0-810-037-471400012292610	10/9/2023	-1.00

What I want to achieve:

Plant	Material	Movement type	Purchase order	Item	key	Posting Date	Quantity	Target Column 1	Target Column 2
6417	0-810-030-805	351	4000121095	70	0-810-030-805400012109570	3/13/2024	8.00		Delete
6417	0-810-030-805	352	4000121095	70	0-810-030-805400012109570	3/13/2024	-1.00		Keep
6417	0-810-030-805	351	4000121095	70	0-810-030-805400012109570	8/8/2023	3.00		Keep
6417	0-810-034-517	351	4000121095	90	0-810-034-517400012109590	8/7/2023	1.00		Keep
6602	0-810-015-654	351	4000121902	10	0-810-015-654400012190210	9/5/2023	2.00		Keep
6602	0-810-015-654	351	4000121902	10	0-810-015-654400012190210	11/8/2023	3.00		Keep
6602	0-810-015-619	351	4000121902	100	0-810-015-6194000121902100	10/3/2023	2.00		Keep
6602	0-810-018-910	351	4000121902	210	0-810-018-9104000121902210	10/18/2023	4.00		Keep
6602	0-810-020-807	351	4000121902	220	0-810-020-8074000121902220	10/18/2023	3.00		Keep
6602	0-810-018-908	351	4000121902	230	0-810-018-9084000121902230	10/18/2023	12.00		Delete
6602	0-810-018-908	352	4000121902	230	0-810-018-9084000121902230	10/19/2023	-6.00		Keep
6602	0-810-018-908	351	4000121902	230	0-810-018-9084000121902230	11/2/2023	6.00		Keep
6482	0-810-037-471	351	4000122926	10	0-810-037-471400012292610	10/9/2023	1.00	Keep
6509	0-810-037-471	351	4000122926	10	0-810-037-471400012292610	10/9/2023	-1.00	Delete

Have been stuck here for 2 days now. Any help will be appreciated!

[rajendraongole1]

Hi @EmmaLiuLiu - Create a Calculated Column for Target Column 1 compare each row's Quantity with other rows having the same Key but different Plant values and opposite Quantity signs. replace the tablename as per your model.

Target_Column_1 =

VAR CurrentKey = 'matl'[key]

VAR CurrentQuantity = 'matl'[Quantity]

VAR CurrentPlant = 'matl'[Plant]

RETURN

    IF (

        COUNTROWS (

            FILTER (

                'matl',

                'matl'[key] = CurrentKey &&

                'matl'[Quantity] = -CurrentQuantity &&

                'matl'[Plant] <> CurrentPlant

            )

        ) > 0,

        "Delete",  // Mark the row for deletion

        "Keep"     // Otherwise, keep the row

    )

create another calculated column to get the Target column 2 this will mark rows with Movement Type 351 where there's a corresponding 352 row, and the 351 row's Posting Date

Target_Column_2 =

VAR CurrentKey = 'matl'[key]

VAR CurrentMovementType = 'matl'[Movement type]

VAR CurrentPostingDate = 'matl'[Posting Date]

RETURN

    IF (

        CurrentMovementType = 351 &&

        // Check if there's a corresponding 352 movement with the same key and later Posting Date

        COUNTROWS (

            FILTER (

                'matl',

                'matl'[key] = CurrentKey &&

                'matl'[Movement type] = 352 &&

                'matl'[Posting Date] >= CurrentPostingDate

            )

        ) > 0,

        "Delete",  // Mark the 351 row for deletion

        "Keep"     // Otherwise, keep the row

    )

you have two different target columns, you can use both to evaluate each row for deletion or retention based on condition. 

Hope this helps.

[EmmaLiuLiu]

Hi @rajendraongole1 , thank you so much for your help! 

The first target column works. However, the second one doesn't work. I spent quite a long time myself to dig into it since my post was marked as spam. I figured one of my requests won't be solved using power dax. If two or more records have the same date and everything else is the same except the movement type (2 with 351 and 1 with 352) and or tracking info that I didn't include in the sample data, I only need to delete one of them. I need to go in there, read the tracking info and identify which record to be deleted. Is there a dax can do this job?

[rajendraongole1 · Accepted solution]

Hi @EmmaLiuLiu - Please check the below calculated column for Target column2

Target Column2 =

IF (

    [Movement type] = 351

    && COUNTROWS (

        FILTER (

            Matl,

            Matl[Key] = EARLIER ( Matl[Key] )

            && Matl[Movement type] = 352

            && Matl[Plant] = EARLIER ( Matl[Plant] )

            && Matl[Posting Date] = EARLIER ( Matl[Posting Date] ) // Same Posting Date

        )

    ) > 0,

    "Delete",

    "Keep"

)

Replace with your table name, Hope this works.