Forum Discussion
Solved
Compare Two Tables - Repeat Rows in Table A for each Name in Table B
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 WeekStartDatesTb...
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 existingtimesheet table to find which rows don't have data.I created a unique keyDateName = FORMAT('AllWeeksAllStaff'[WeekStart], "YYYYMMDD") & 'AllWeeksAllStaff'[Staff_FullName]and also a similar one in the existing timesheets tableDateName = 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, andtimesheet ID. Missing ID's will show where there is no timesheet
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 tableDateName = 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
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.
