Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi PWR BI Family ❤,
I would like to create a report for managers, so that they can check which staff have missing timesheets.
I would like to know for every [WeekStart] in my WeekStartDatesTbl; if a staff member has submitted their timesheet or not.
I have three tables to start with and I would like to create a fourth.
Find the data and tables here: PWRBI_missingtimesheets.pbix
Starting tables:
Table A: WeekStartDatesTbl
Table B: StaffNamesTbl
Table C: TimesheetsTbl
I would like hard data (i.e. not the output of a formula). I would prefer that the solution leverage PowerQuery and create a new table (Table 😧 MissingTimesheetsTbl. However if my PWR BI Family ❤ thinks otherwise; no worries, I’ll go with a DAX solution.
I envision a table as follows:
StaffMemberName column: a list of staff names (derived from the staff table) in column A.
Week Start Dates: Week start dates in column B (dates start on Monday, and would be repeated for each staff member)
TimesheetSubmittedYN column: a look up against the Timesheet table, which checks if for Staff member (e.g. Jo Name) a row exists for the corresponding week start date. If a timesheet exist, out "Yes", if no timesheet exist out "No", if the date is in the future i.e. date greater than now() + 7 days out put "Pending"
TimesheetID Column: the ID number of the timesheet from the timesheet table, if none exist output “Missing Sheet”; if the date is in the future i.e. date greater than now() + 7 days output "Pending".
Solved! Go to Solution.
I had a little play around with this.
I created a new table
AllWeeksAllStaff = CROSSJOIN(WeekStartDatesTbl, StaffNamesTbl)That gives you all the possible staff timesheets. You then have to link up with the existing
DateName = FORMAT('AllWeeksAllStaff'[WeekStart], "YYYYMMDD") & 'AllWeeksAllStaff'[Staff_FullName]and also a similar one in the existing timesheets table
DateName = FORMAT(TimesheetsTbl[WeekCommencing], "YYYYMMDD") & TimesheetsTbl[StaffMemberName]You then create a relationship between them, on this field.
I had a little play around with this.
I created a new table
AllWeeksAllStaff = CROSSJOIN(WeekStartDatesTbl, StaffNamesTbl)That gives you all the possible staff timesheets. You then have to link up with the existing
DateName = FORMAT('AllWeeksAllStaff'[WeekStart], "YYYYMMDD") & 'AllWeeksAllStaff'[Staff_FullName]and also a similar one in the existing timesheets table
DateName = FORMAT(TimesheetsTbl[WeekCommencing], "YYYYMMDD") & TimesheetsTbl[StaffMemberName]You then create a relationship between them, on this field.
This is a good solution @HotChilli thank you. Could I possibly leave this problem open in favour of a PowerQuery solution. I would really like the cross joined table to be created at run time as physical table.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |