cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
StaceyMcCain Regular Visitor
Regular Visitor

Compare values from columns in two tables, identify those that don't match

Hello,

 

I have two tables reflecting time entry for employees.  The tables are joined by the employee ID.  I am trying to create a measure to compare the time entry in both tables and identify those where different amounts were entered.  Below is a mock-up of what I have....

 

Table 1    Table 2   
Employee #NameDateHours Employee #NameDateHours
1000Joe3/1/20198 1000Joe3/1/20198
1001Mike3/3/20197 1001Mike3/3/20195
1002Sue3/2/20198 1002Sue3/2/20196
1003Lee3/4/20193 1003Lee3/4/20193
1004Kim3/15/20195 1004Kim3/15/20195
1005John3/10/20191 1005John3/10/20192

 

I need a measure which will compare the "Hours" from each table and return those where they do not match.  This could be by assigning a match/doesn't match value (e.g. Y/N) and I could filter the report for the "not match" value.

 

Ideas?

 

Thanks!

 

Stacey

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M New Contributor
New Contributor

Re: Compare values from columns in two tables, identify those that don't match

You can go down the Power Query route with this one as well:

  1. Merge Table 2 with Table 1 ( full outer, on Employee # and Date)
  2. Merge.png
  3. Expand Table1 Hours
  4. Expand.png
  5. Add a column to Subtract Hours in Table2 with the Hours from Table 1
  6. Filter out that new column on values <> 0

Final Table.png

 

You can do the same type of thing in DAX as well, but just find this easier in Power Query

 

6 REPLIES 6
rojan1918 Frequent Visitor
Frequent Visitor

Re: Compare values from columns in two tables, identify those that don't match

@StaceyMcCain I would use an if-statement:

 

IF(Table1[Hours]=Table2[Hours],"Match","No Match")

StaceyMcCain Regular Visitor
Regular Visitor

Re: Compare values from columns in two tables, identify those that don't match

Thank you for the suggestion, @rojan1918 !  I should have mentioned that both tables have numerous entries per employee, and there are not the same entries per employee per table.  Essentially I need to compare the Employee # and Date and, when it finds a match, then compare the hours.  Do you think your approach will work in this instance?

 

Thanks again!

Super User
Super User

Re: Compare values from columns in two tables, identify those that don't match

@StaceyMcCain  how you have setup the relation between these two tables? Is there unique key? Is it 1-1 relationship?





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


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




rojan1918 Frequent Visitor
Frequent Visitor

Re: Compare values from columns in two tables, identify those that don't match

@StaceyMcCain if I understand you correctly you basically just need to check if the Employee # column, the date column and the hours column match. If any of these three doesn't match then the result should be "No Match". We just need to adjust the if-statement a little

 

IF(Table1[Hours]=Table2[Hours] && Table1[Date]=Table2[Date] && Table1[Employee #]=Table2[Employee #],"Match","No Match")

Nick_M New Contributor
New Contributor

Re: Compare values from columns in two tables, identify those that don't match

You can go down the Power Query route with this one as well:

  1. Merge Table 2 with Table 1 ( full outer, on Employee # and Date)
  2. Merge.png
  3. Expand Table1 Hours
  4. Expand.png
  5. Add a column to Subtract Hours in Table2 with the Hours from Table 1
  6. Filter out that new column on values <> 0

Final Table.png

 

You can do the same type of thing in DAX as well, but just find this easier in Power Query

 

StaceyMcCain Regular Visitor
Regular Visitor

Re: Compare values from columns in two tables, identify those that don't match

I ended up creating an appended table with the data from both of the previously mentioned tables.  I'm now getting the results I was looking for.