cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Super User IV
Super User IV

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)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Super User IV
Super User IV

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)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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"

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors