Hi everyone,
My dataset consists of a single fact table containing both employee-level data (e.g., what department and practice thei belog to) and project-related data (available hours, billable hours etc).
I currently filter the dashboard using the following employee-level fields:
- Employee Practice – values: Practice 1, Practice 2
- Employee Service Line – values: SL 1, SL 2, SL 3
- Employee Region – values: R1, R2, R3
In addition, I have corresponding project-level fields:
- Project Practice
- Project Service Line
- Project Region
These fields contain the same values as their employee-level counterparts.
My Goal
I want to create a calculated measure (e.g., Billable Hours) that:
- Ignores any visual filters applied to the employee fields (Practice, Service Line, Region).
- Uses the selected filter values (via parameters) to dynamically filter the project fields (Project Practice, Project Service Line, Project Region).
In other words:
If a user selects Employee Practice = Practice 1 and Employee Service Line = SL 2,
I want the measure to:
Ignore these filters so it evaluates all employees, not just those in Practice 1 or SL 2.
Filter the sum only to projects where Project Practice = selected Employee Practice (in this example, Practice 1) and Project Service Line = selected Employee Service Line (in this example SL 2)
I feel I need to do a mix of parameters, filters and sumOver with PRE_AGG, but for some reason its not working as it
s supposed to.
Any help would be appreciated.