The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
88 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |