Compare Two Tables - Repeat Rows in Table A for each Name in Table B

[Josh_BI_UK]

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

Current Tables

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".

I would like to create Table 😧 MissingTimesheetsTbl

[HotChilli · Accepted 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

timesheet table to find which rows don't have data.

I created a unique key

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.

You can then construct a table visualisation with all the timesheet dates, staff, and

timesheet ID. Missing ID's will show where there is no timesheet

[Josh_BI_UK]

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.