Forum Discussion
Helper IHow to identify a row based on another row?
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:
- 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.
- 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!
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.
3 Replies
- rajendraongole1
Super User
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]RETURNIF (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 DateTarget_Column_2 =VAR CurrentKey = 'matl'[key]VAR CurrentMovementType = 'matl'[Movement type]VAR CurrentPostingDate = 'matl'[Posting Date]RETURNIF (CurrentMovementType = 351 &&// Check if there's a corresponding 352 movement with the same key and later Posting DateCOUNTROWS (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.
EmmaLiuLiuHi 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
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.