Filter measure by selected employee values without applying visual filters in QuickSight

Question & Answer
, , , , ,
1

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.

:white_check_mark: 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:
:cross_mark: Ignore these filters so it evaluates all employees, not just those in Practice 1 or SL 2.
:white_check_mark: 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 its supposed to.

Any help would be appreciated.

2

Hi @DianaC,

You need to use PRE_FILTER to ignore the filters at the visual level and use ifelse in your calculated field for Project Practice, Project Service Line and Project Region.

Can you show what your calculated field currently looks like?

4

sumOver(
ifelse(
{COGs Type} = ‘COGs Shift’
AND
({Project Sub Region} = ${EmployeeSubregion}
OR {Project Service Line} = ${EmployeeServiceLine}),
{Billable Hours},
0
),
[Date, {COGs Type}],
PRE_AGG
)

5

Can you try this calculated field?

sumOver(
    ifelse({Project Sub Region} = ${EmployeeSubregion} OR {Project Service Line} = ${EmployeeServiceLine}, {Billable Hours}, 0),
    [Date, {COGs Type}],
    PRE_FILTER 
)