Forum Discussion
Identify duplicate values with different values
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#
| 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# |
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]))
5 Replies
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]))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 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.
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
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.
