Crosstab Display with more rows displayed than there are records in table

I have a table called Workers and a table called TimeSheets (relevant to this question). To facilitate scheduling I have built a display that lists my workers in the first column and then a column for each day of the week.

To accomplish this, I used the Workers table and added a virtual column for each day extracted from a Slice of the selected week’s TimeSheets table, against each employee like:

[Monday]= SELECT(SelectedWeek[Job], AND(([Worker] = [_THISROW].[Worker]) , Weekday([Work Date])=2))

It works but only lists one entry per day because my Workers table only has each employee listed once. If an employee has two timesheet entries for one date (e.g. 7:00 – 13:00 at Job A and 13:00 -17:00 at Job B) then only one will display.

I can’t think of any way to do it, but I hoped someone in the community may think of a way.

Not possible. :frowning:

1 Like

I thought not. Is there a way to create a temp table with more records? Or is there a way to have a table display multiple lines per record?

You cannot create temporary tables, but you can populate tables with temporary rows.

Nope.

Could you visually demonstrate what you want? I’m not sure I’ve fully grasped your goal.

I’ve attached a screen shot of what my draft copy of the week view looks like. It shows names, and what job they’ve been assigned to do. Only trouble is it only shows one line so if they’re doing more than one job per day you don’t know unless you click on the name to see the list. Names have been deliberately obfuscated.

Have you considered basing this view on the TimeSheet table itself rather than the Workers table?

2 Likes

I have been trying to think of how that would work. The Timesheet table has one entry per job. i.e.
Name
Date
Start Time
End Time
Job Details
I don’t know how to cross tabulate that to list names down and dates across.

To further build on from @Steve’ s guidance of using timesheets table, you may wish to evlauate following workaround option. It sounds from the image you shared that you are using dashboard in desktop mode for viewing the weekly schedule.

Please build a slice for each day of the week with an expression something like WEEKDAY([Date])=2 for Monday , WEEKDAY([Date])= 3 for Wednesday and so on. Each slice may simply have Name and Job Details as two columns,

Then you can base your dashboard on the seven slices, once for each day.

This approach does not require daywise columns to be created but daywise slices. Also if there are more than one entry on a particular day for a person, it gets shown, as highlighted. Finally since all dayswise views are side by side, a good overview of a person 's weekly schedule is available at one glance.

There can be of course smarter and alternative approaches. Just thought of sharing an approach. Hope it helps.

3 Likes

I see what you’re getting at. Thanks for the suggestion. It would work but the view is a bit messy. Hard to see at a glance what an employee’s week looks like, but worth considering, even if it’s an optional view. Appreciate your help.

2 Likes

You are welcome Rob.