Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
You can go down the Power Query route with this one as well:
You can do the same type of thing in DAX as well, but just find this easier in Power Query
@Anonymous I would use an if-statement:
IF(Table1[Hours]=Table2[Hours],"Match","No Match")
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!
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 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")
You can go down the Power Query route with this one as well:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |