Is this Possible? - Comparing Two Sets of Data Between the Same Table

[mangom02]

Hi,

I'm trying to come up with a formula that compares two sets of data in the same table. For example here is some sample data:

Identifier1,Identifier2,Concatenate,Date,Unique ID
8.8.8.8,1234,8.8.8.81234,1/12/20,1
8.8.8.8,4321,8.8.8.84321,1/12/20,1
1.2.3.4,1234,1.2.3.41234,1/12/20,1
1.2.3.4,4321,1.2.3.44321,1/12/20,1
8.8.8.8,1234,8.8.8.81234,1/20/20,2
8.8.8.8,4321,8.8.8.84321,1/20/20,2
8.8.8.8,1234,8.8.8.81234,1/30/20,3
8.8.8.8,4321,8.8.8.84321,1/30/20,3
1.2.3.4,1234,1.2.3.41234,1/30/20,3
1.2.3.4,4321,1.2.3.44321,1/30/20,3
4.3.2.1,1234,4.3.2.11234,1/30/20,3

What I want to do I check and see if any of the concatenated values are duplicates but only from the previous date (Unique ID -1). Let me know if this makes sense.

I want to do this with Power BI bc I will be adding new files to the data every week, so my hope is that it will automatically add that calculations between weeks to see what's duplicate this week from last week, as well as what is new.

[Greg_Deckler · Accepted solution]

So I am thinking perhaps something like the following:

Column Duplicate = 
  VAR __UniqueID = 'Table'[UniqueID]
  VAR __Table = 
    SELECTCOLUMNS(
      FILTER('Table','Table'[UniqueID] = __UniqueID - 1),
      "__Concatenate",'Table'[Concatenate]
    )
  VAR __Current = 'Table'[Concatenate]
RETURN
  IF(__Current IN __Table,"Yes","No)

[Greg_Deckler]

So basically:

1. Create a variable __UniqueID that gets the current row's value for UniqueID column
2. Create a table variable called __Table that has a single column, which is all of the values from the Concatenate column for the UniqueID that is one less than the current row's value for UniqueID (__UniqueID)
3. Create a variable __Current that gets the current row's value for the Concatenate column
4. Check to see if the value for __Current is in the single column table, __Table. If so, return "Yes", it is a duplicate. Otherwise, return "No"