Identify duplicate values with different values

[Bmejia]

I am trying to identify if a employeed submitted the same expense amount in two different orders or in the same order.  I have builded this measure column to filter down in my visual if the same amount is in in the same order.  I tried doing a combo of both but it didn't work, and I understand that it has to be either one, so now I am trying to split it into two filters to make it much simpler, but I am having issues identifying (Filter1) which is looking for the same amount in a different orders.

--If Greater than 1 then "Same Amount in the Same Order#

New =

COUNTROWS(

    FILTER(Table1,

        Table1[orders]=EARLIER(Table1[orders])

            && EARLIER(Table1[Amount])=Table1[Amount]))

--using this to identify all duplicates

Duplicates =

IF (

    CALCULATE (

        COUNTROWS ( Table1),

        ALLEXCEPT ( Table1, Table1[Employee], Table1[Amount], Table1[Description])

    ) = 1,

    0,

    1

Employee	Decription	Orders	TRANSDATE	Amount	My Filter Part1	My Filter part 2(for visual)	Filter1( I would like to see)	filter2(  2nd Filter I would like to see)
John	Miles	Ord-U088518	5/24/2022	1.12	2	Same Amount in the Same Order#	Same Amount in Different Order#	Same Amount in the Same Order#
John	Miles	Ord-U088518	5/25/2022	1.12	2	Same Amount in the Same Order#	Same Amount in Different Order#	Same Amount in the Same Order#
John	Miles	Ord-U088660	6/15/2022	1.12	1	Same Amount in Different Order#	Same Amount in Different Order#
John	Accident	Ord-U088464	7/11/2022	5	2	Same Amount in the Same Order#	Same Amount in Different Order#	Same Amount in the Same Order#
John	Accident	Ord-U088464	7/18/2022	5	2	Same Amount in the Same Order#	Same Amount in Different Order#	Same Amount in the Same Order#
John	Accident	Ord-U088765	7/22/2022	5	1	Same Amount in Different Order#	Same Amount in Different Order#
John	Accident	Ord-U088518	6/6/2022	20	1	Same Amount in Different Order#
John	Accident	Ord-U088660	6/14/2022	20	1	Same Amount in Different Order#
John	Lunch	Ord-U088464	7/11/2022	46	2	Same Amount in the Same Order#	Same Amount in Different Order#	Same Amount in the Same Order#
John	Lunch	Ord-U088464	7/18/2022	46	2	Same Amount in the Same Order#	Same Amount in Different Order#	Same Amount in the Same Order#
John	Lunch	Ord-U088665	7/22/2022	46	1	Same Amount in Different Order#	Same Amount in Different Order#
John	Miles	Ord-U088518	6/1/2022	48.16	2	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Miles	Ord-U088518	6/3/2022	48.16	2	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Miles	Ord-U088518	5/23/2022	96.88	2	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Miles	Ord-U088518	5/26/2022	96.88	2	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Miles	Ord-U088464	7/11/2022	117.6	2	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Miles	Ord-U088464	7/18/2022	117.6	2	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Dinner	Ord-U088518	5/23/2022	136	2	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Dinner	Ord-U088518	6/6/2022	136	2	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Hotel	Ord-U088518	5/24/2022	155.82	6	Same Amount in the Same Order#		Same Amount in the Same Order#
John	Hotel	Ord-U088518	5/25/2022	155.82	3	Same Amount in the Same Order#		Same Amount in the Same Order#

[Bmejia]

HoangHugo solution works, but once i implemented with production data which is alot more data, it is taking a long time to load.  I had to stopped.   is there a more efficient way to get this resolve where it does not take a toll on the data load performance.

[Bmejia]

I summarized the data and filter it down the data to only to what need it to be scan unfortunately still having same issue.  I am down to around 165,000 and still causing the issue.  I do have other three columns where i am using the earlier function but using "=" rather "<>"  and they ran find once i removed the column.

TABLE2 =

CALCULATETABLE(

 SUMMARIZE(TABLE1,TravelExpenses[Amount],TABLE1[Order],TABLE1[Description],Table1[Employee]),TABLE1[Duplicates]=1)

Thanks any feedback would be appreciated.

[HoangHugo]

Sure, Microsoft advices EARLIER may take long time. In your case, scanning entire table is required, but I suggest you use SUMMARIZE make a new table to reduce rows that system have to scan

[Bmejia]

Thanks Hoang, I just added another earlier statatement to breakout the data by all the employees in my table as my example only included one employee.

New =

COUNTROWS(

    FILTER(Table1,

        Table1[orders]<>EARLIER(Table1[orders])

            && EARLIER(Table1[Amount])=Table1[Amount])

            && EARLIER(Table1[Employee])=Table1[Employee]))

[HoangHugo · Accepted solution]

Hi,

I understand, in filter 1, you look up new orders have same amount, so it should be:

New =

COUNTROWS(

    FILTER(Table1,

        Table1[orders]<>EARLIER(Table1[orders])

            && EARLIER(Table1[Amount])=Table1[Amount]))