Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All
I've tried to do this numerous ways (M Query using table Merges and indexes, and DAX using SUMMARIZE) and come up short.
I have two tables, and have cut this list down to the relevant markers (the UserName, ProjectName and ClientName exists but isn't relevant)
Timesheets
UserID | ProjectID | ClientID | Comment | Date | Hours |
1 | 1 | 1 | Wrote Proposal | 27 March 2023 | 0.5 |
1 | 2 | 2 | Drafted Memo | 28 March 2022 | 0.9 |
2 | 3 | 2 | Meeting | 28 March 2023 | 2 |
2 | 1 | 1 | Prepared for Meeting | 29 March 2023 | 1 |
2 | 3 | 2 | Edited Document | 29 March 2023 | 0.5 |
3 | 3 | 2 | Called Client | 30 March 2023 | 3 |
3 | 3 | 2 | Meeting | 30 March 2023 | 5 |
Scheduled Hours
UserID | ProjectID | ClientID | WeekStart | Hours |
1 | 1 | 1 | 27 March 2023 | 5 |
2 | 2 | 2 | 27 March 2023 | 5 |
3 | 3 | 2 | 27 March 2023 | 5 |
What I need to do is have the actual hours schedule against the aggregate. In this case it would look like this:
UserID | ProjectID | ClientID | WeekStart | Scheduled Hours | Actual |
1 | 1 | 1 | 27 March 2023 | 5 | 0.5 |
2 | 2 | 2 | 27 March 2023 | 5 | 0.0 |
3 | 3 | 2 | 27 March 2023 | 5 | 8 |
1 | 2 | 2 | 27 March 2023 | 0 | 0.9 |
2 | 3 | 2 | 27 March 2023 | 0 | 2.5 |
2 | 1 | 1 | 27 March 2023 | 0 | 1 |
Can anyone help please?
if that gives you the correct result, then the index will be the correct solution to the duplication problem
watch my video
https://1drv.ms/v/s!AiUZ0Ws7G26Rhw1csRNGZk9XmquL?e=pDz07f
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rhwx7s0biyiBlfb0l?e=coFhpr
Hey @Ahmedx ,
Thank you so much for your prompt solution!
Regarding the above, I've found it caused a masssive duplication (several million) of records so I created an index before I filtered which seemed to bring the records down to a level I'd expect (There are 210,000 Timesheet records and 200,000 Resourcing rows, and the aggregated records are around 180,000).
Would the index be the correct solution to the duplication problem?
Many thanks
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |