Create table dynamically for use in PATH Statement

[ConnieMaldonado]

Hello - I'm trying to use a path statement on an Employee table to set up Row Level Security.

The employee table is updated daily and goes back 15 years. It contains active and terminated employees.
I have employee ID and Supervisor ID for each employee.  Here is mock data representing the employee table:

EMPLOYEE_NUMBER	EMPLOYEE_NAME	SUPERVISOR_EMPLOYEE_NUMBER	SUPERVISOR_NAME	EMPLOYEE_TERMINATION_DATE	SUPERVISOR_TERMINATION_DATE
123456ABC	John Doe	555879LLL	Angelina Jolie
234567DEF	James Smith	666454KLM	Sam Heughan
345678GHI	Jorge Rodriguez	659874STU	Tom Cruise		1/2/2019
456789JKL	Julia Roberts	258147PQR	Brad Pitt		1/9/2016
567890MNO	Jennifer Lopez	123456ABC	John Doe
258147PQR	Brad Pitt	555879LLL	Angelina Jolie	1/9/2016
659874STU	Tom Cruise	258147PQR	Brad Pitt	1/2/2019	1/9/2016
666454KLM	Sam Heughan	456789JKL	Julia Roberts
888457FGH	Joan Crawford	659874STU	Tom Cruise		1/2/2019
985645DFD	Tom Hanks	659874STU	Tom Cruise		1/2/2019
457896DNL	Will Smith	456789JKL	Julia Roberts
754699FSJ	Denzel Washington	456789JKL	Julia Roberts
897333FDS	George Clooney	666454KLM	Sam Heughan

I want to use CALCULATETABLE to create a table from the Employee table to be used for RLS, and I only want to include active employees.

My issue is that some active employees have supervisors who are terminated. For one reason or another, the data is not updated, probably because a new supervisor has not yet been assigned to the area.

When I create the active employee table (using CalculateTable), if I exclude terminated employees, then I lose the record for the supervisors who are terminated, creating an issue in the PATH statement.

************************************************************************************************************************************

I am using the following to create the active table:

ACTIVE Employees = CALCULATETABLE('POC Mock Data',ISBLANK('POC Mock Data'[EMPLOYEE_TERMINATION_DATE]))

I then create a PATH statement as follows:

PATH = PATH('ACTIVE Employees'[EMPLOYEE_NUMBER],'ACTIVE Employees'[SUPERVISOR_EMPLOYEE_NUMBER])

I receive the following error for the PATH statement:

The value '555879LLL' in 'ACTIVE Employees'[SUPERVISOR_EMPLOYEE_NUMBER] must also exist in 'ACTIVE Employees'[EMPLOYEE_NUMBER]. Please add the missing data and try again.

Because I'm only including active employees in the ACTIVE Employees table, I am excluding records for any terminated supervisors.

Since I can't count on the consistent integrity of the employee table (outside of my domain), I'm trying to create an active table for which I can use the PATH statement for RLS.  Even if I have to exclude active records; that's ok, there shouldn't be that many.  To use the PATH statement, there must be a record in the table for all supervisors.

How do I dynamically create a table from the employee table that contains either:
Active employees OR
Terminated employees who are listed as the supervisor for an active employee

I only want to include a record in the table if the supervisor on that record also has a record in the table (confusing, I know).

I thought about creating a logical column titled "Supervisor for Active Employee", so I could then create the active table by filtering on employee termination date and the logical column. However, I ran into a myriad of issues trying to do this, primarily because a supervisor can be listed for many employees (I used lookupvalue which is inconclusive when you have multiple records that meet the lookup criteria). I couldn't think of any other way to do it.

I also created a table of unique supervisor employee numbers and tried to run that back against the employee table, but I ran into circular issues there.

I've tried a slew of other things, too many to list here.

Any help would be appreciated!  Thanks.

[DataInsights · Accepted solution]

@ConnieMaldonado,

Try this solution. I added a few rows to the sample data.

Calculated column in table All Employees:

Include in RLS = 
VAR vEmpNum = 'All Employees'[EMPLOYEE_NUMBER]
VAR vTable =
    FILTER (
        'All Employees',
        'All Employees'[SUPERVISOR_EMPLOYEE_NUMBER] = vEmpNum
            && ISBLANK ( 'All Employees'[EMPLOYEE_TERMINATION_DATE] )
    )
VAR vResult =
    IF ( ISBLANK ( 'All Employees'[EMPLOYEE_TERMINATION_DATE] ) || COUNTROWS ( vTable ), 1 )
RETURN
    vResult

Calculated table:

Active Employees = 
CALCULATETABLE ( 'All Employees', 'All Employees'[Include in RLS] = 1 )