Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

@Anonymous I would use an if-statement:

 

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

Anonymous
Not applicable

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
Not applicable

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
Not applicable

@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
Not applicable

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

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.