If duplicate, how to compare other columns?

[EmmaLiuLiu]

Hi gurus:

I've been working on this since last night. I have this data table. The Key column is concat value of Plant, Material, Purchase Order and Item columns. If there are duplicates in column Key, it's "351" in column Movement Type, and the date in Posting Date is on the same or an earlier date than the other duplicate with "352" in column Movement Type, then mark "Delete", otherwise mark "Keep".

Plant	Material	Movement type	Purchase order	Item	key	Posting Date
6417	0-810-030-805	351	4000121095	70	64170-810-030-805400012109570	3/13/2024
6417	0-810-030-805	352	4000121095	70	64170-810-030-805400012109570	3/13/2024
6417	0-810-033-427	351	4000121095	80	64170-810-033-427400012109580	8/8/2023
6417	0-810-034-517	351	4000121095	90	64170-810-034-517400012109590	8/7/2023
6602	0-810-015-654	351	4000121902	10	66020-810-015-654400012190210	9/5/2023
6602	0-810-015-654	351	4000121902	10	66020-810-015-654400012190210	11/8/2023

The final column I'd like to add is like this:

Plant	Material	Movement type	Purchase order	Item	key	Posting Date	Added Column
6417	0-810-030-805	351	4000121095	70	64170-810-030-805400012109570	3/13/2024	Delete
6417	0-810-030-805	352	4000121095	70	64170-810-030-805400012109570	3/13/2024	Keep
6417	0-810-033-427	351	4000121095	80	64170-810-033-427400012109580	8/8/2023	Keep
6417	0-810-034-517	351	4000121095	90	64170-810-034-517400012109590	8/7/2023	Keep
6602	0-810-015-654	351	4000121902	10	66020-810-015-654400012190210	9/5/2023	Keep
6602	0-810-015-654	351	4000121902	10	66020-810-015-654400012190210	11/8/2023	Keep

Is it something achievable in power bi? 

[muhammad_786_1 · Accepted solution]

Hi @EmmaLiuLiu 

Does your data contain only Movement Types '351' and '352'

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

Best Regards,
Muhammad Yousaf

[Gsssidhu]

and its not a measure, you are creating a new column. let me know your table name so that i can post the exact DAX that you need to use.

[Gsssidhu]

Keep adding the condition in the filter function

Filter(
TableName,
TableName[Key] = Earlier(TableName[Key]) &&
TableName[Posting_Date] <= Earlier(TableName[Posting_Date])&&
TableName[Movement_Type] <> Earlier(TableName[Movement_Type])
)

[EmmaLiuLiu]

It's still not working. I'm going to post my power bi desktop file here so you or other gurus can help test it out. Thanks again!

[Gsssidhu]

Key Column, that is what you are looking for duplicates, Right?

[EmmaLiuLiu]

I meant column, not meausre.

Unfortunately it doesn't work. It deleted all duplicate records. I only want these rows to be deleted:

1. It has to be a duplicate in column Key
2. Then the posting date of that row has to be on the same or an earlier date compared to the one in row with “352” in column “movement type”. In other words, if there is no 352, then keep the records. If there is 352, only delete the one that has a “no later posing date” record.

Can you please help me😩?

[Gsssidhu]

 Try This

Duplicate_Flag
If
(
Countrows(
Filter(
TableName,
TableName[ColumnName] = Earlier(TableName[ColumnName])
)
)>1, "Delete", "Keep")

[EmmaLiuLiu]

Thanks for the response @Gsssidhu ! 

I have all columns marked with names. So what's the "ColumnName" you were refering to in your meausre?