Forum Discussion

Bmejia's avatar
Bmejia
Icon for Super User rankSuper User
4 years ago
Solved

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 amou...
Need Help
  • HoangHugo's avatar
    HoangHugo
    4 years ago

    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]))
Bmejia's avatar
Bmejia
Icon for Super User rankSuper User
4 years ago

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.

  • HoangHugo's avatar
    HoangHugo
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    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's avatar
    Bmejia
    Icon for Super User rankSuper User
    4 years ago

    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.