Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Josh_BI_UK
Helper II
Helper II

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 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 TablesCurrent 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 D: MissingTimesheetsTblI would like to create Table 😧 MissingTimesheetsTbl

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.