Please help, need Creative Drill Through Options

Hi DadJokeMaster ,

To create a more powerful and flexible employee workload analysis page, it's important to move beyond a simple drillthrough that only shows assigned jobs. By incorporating a disconnected calendar table and using task start and end dates, you can enable a timeline view that reveals how jobs are distributed across days, weeks, or months. This helps leadership identify overlaps, bottlenecks, or underutilization.

Start by building a disconnected calendar table that spans the full range of job durations and includes grouping columns using ADDCOLUMNS:

Calendar = 
ADDCOLUMNS(
    CALENDAR(MIN('Jobs'[StartDate]), MAX('Jobs'[EndDate])),
    "YearMonth", FORMAT([Date], "YYYY-MM"),
    "Week", WEEKNUM([Date])
)

This calendar is not related directly to your Jobs table. Instead, it is used as the basis for analyzing workload over time. Since tasks span multiple dates, we calculate active jobs for each date by checking whether the date falls between the task's start and end date. To isolate the workload for a selected employee, create a disconnected NavigationEmployee table like this:

NavigationEmployee = DISTINCT(SELECTCOLUMNS('Jobs', "EmployeeID", 'Jobs'[EmployeeID], "EmployeeName", 'Jobs'[EmployeeName]))

Then build a measure that shows how many jobs are active on any given date for the selected employee:

ActiveJobCount = 
CALCULATE (
    DISTINCTCOUNT('Jobs'[JobID]),
    FILTER (
        'Jobs',
        MAX('Calendar'[Date]) >= 'Jobs'[StartDate]
        && MAX('Calendar'[Date]) <= 'Jobs'[EndDate]
    ),
    TREATAS(VALUES('NavigationEmployee'[EmployeeID]), 'Jobs'[EmployeeID])
)

Place a slicer using the NavigationEmployee table on both the job detail page and the employee workload page, and sync them via the "Sync slicers" view. On the employee workload page, use the Calendar table as the axis in a line chart, area chart, or matrix to display ActiveJobCount across time. This lets leadership visually grasp how heavily an employee is scheduled over the coming days or months.

Instead of using a drillthrough (which passes along filters you don’t want), add a navigation button on the job detail page. When a user selects an employee from a table or card, the synced slicer captures that employee, and the button takes them to the workload page, which now shows a time-distributed job view based solely on that employee's assignments—no over-filtering from job-specific fields.

This method provides a much richer, timeline-aware view of employee workload, enabling smarter resource decisions while preserving flexibility and clean filter context in your Power BI report.

Best regards,