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

[Anonymous]

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 #	Name	Date	Hours		Employee #	Name	Date	Hours
1000	Joe	3/1/2019	8		1000	Joe	3/1/2019	8
1001	Mike	3/3/2019	7		1001	Mike	3/3/2019	5
1002	Sue	3/2/2019	8		1002	Sue	3/2/2019	6
1003	Lee	3/4/2019	3		1003	Lee	3/4/2019	3
1004	Kim	3/15/2019	5		1004	Kim	3/15/2019	5
1005	John	3/10/2019	1		1005	John	3/10/2019	2

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

[Anonymous]

@Anonymous I would use an if-statement:

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

[Anonymous]

Thank you for the suggestion, @Anonymous !  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!

[Anonymous]

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.

[Anonymous]

@Anonymous 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")

[Anonymous · Accepted solution]

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. 
3. Expand Table1 Hours
4. 
5. Add a column to Subtract Hours in Table2 with the Hours from Table 1
6. Filter out that new column on values <> 0

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

[parry2k]

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