Filter with multiple conditions

[LeahGJ]

Hi,

I have a listing of employees salary payments for 2022. I only want to filter employees who have been paid contract gratuity in the corresponing period, including all other allowance fields. An example of the data is below.

Simply, I want the results to return employee #109500 with values for cycle_no. 1 only, since in cycle_no. 1, pay_year 2022,  the employee was paid both contract graturty and another allowance. Note that 'Element Name' is the allowance field.  

EMPLOYEE_NUMBER	PAYROLL_CLASS	PAY_YEAR	CYCLE_NO.	CLASSIFICATION_NAME	ELEMENT_NAME	PAY_VALUE
109500	Calendar Month	2022	1	Earnings	Basic Salary Monthly	22848
109500	Calendar Month	2022	1	Earnings	CONTRACT GRATUITY 066	82252.8
109500	Calendar Month	2022	2	Earnings	Basic Salary Monthly	22848
379002	Calendar Month	2022	1	Earnings	Basic Salary Monthly	22848
379002	Calendar Month	2022	2	Earnings	Basic Salary Monthly	22848
169090	Calendar Month	2022	1	Earnings	Basic Salary Monthly	22848
169090	Calendar Month	2022	2	Earnings	Basic Salary Monthly	22848

[lbendlin]

You need to do this via a measure* as it is a "filtering up"  pattern.

First find the employees that have contract gratuity.  Then find all the payment data for these employees.

* technically a calculated column would also work since these facts are immutable.

[LeahGJ]

Thanks Ibendlin. can you share a meaure for this calculated column please. 

[lbendlin]

please indicate the expected outcome based on the sample data you provided.

[LeahGJ]

this is my expected outcome:

EMPLOYEE_NUMBER	PAYROLL_CLASS	PAY_YEAR	CYCLE_NO.	CLASSIFICATION_NAME	ELEMENT_NAME	PAY_VALUE
109500	Calendar Month	2022	1	Earnings	Basic Salary Monthly	22848
109500	Calendar Month	2022	1	Earnings	CONTRACT GRATUITY 066	82252.8