Azure Analysis Services (AAS) - Defining A Role with Dynamic Table Filter

After doing some research for a client, the best way to implement the security is to use Roles in Azure (SQL Server Analysis Services).  This task is to replace an existing Oracle OBIEE with Power BI & AAS.

I have created a development environment with the model seen below.  The three disconnected tables (roles, team hierarchy, and office hierarchy) are included for security.

Two flavors of the first report apply (TEAM/OFFICE) security to either the EMPLOYEE or PROJECT table.  Therefore, two roles with a complex DAX formulas are required.

The issue can be found on the last two variables in the code below.  They are hardcoded to use HIER3_CODE and HIER_CODE respectively.  This works for only one case. However, they need to be dynamic since there are 17 different columns (levels) of possible values for the 2000+ users.  I used “DILBERT” to replace USERNAME() that will be used in the finally version of the code.  Of course, for testing I am just picking a windows AD user name.

In short, can this dynamic look up of a field given a variable be done with DAX? 

--
-- DAX FILTER
--

DEFINE 

-- NAME OF COLUMN
VAR TEAM_NODE = CALCULATE(
    DISTINCT(WC_S_EMPLOYEE_ROLES_D[TEAM_HIERARCHY_NODE]),
    WC_S_EMPLOYEE_ROLES_D[USERNAME]= "DILBERT", 
    WC_S_EMPLOYEE_ROLES_D[ACTIVE_FLAG]="Y"
    ) 

-- VALUE IN COLUMN
VAR TEAM_VALUES = CALCULATETABLE(
    DISTINCT(WC_S_EMPLOYEE_ROLES_D[EMP_TEAM_CEILING]),
    WC_S_EMPLOYEE_ROLES_D[USERNAME]= "DILBERT", 
    WC_S_EMPLOYEE_ROLES_D[ACTIVE_FLAG]="Y"
    ) 
    
-- NAME OF COLUMN
VAR OFFICE_NODE = CALCULATE(
    DISTINCT(WC_S_EMPLOYEE_ROLES_D[OFFICE_HIERARCHY_NODE]),
    WC_S_EMPLOYEE_ROLES_D[USERNAME]= "DILBERT", 
    WC_S_EMPLOYEE_ROLES_D[ACTIVE_FLAG]="Y"
    )

-- VALUE OF COLUMN
VAR OFFICE_VALUES = CALCULATETABLE(
    DISTINCT(WC_S_EMPLOYEE_ROLES_D[EMP_OFFICE_CEILING]),
    WC_S_EMPLOYEE_ROLES_D[USERNAME]= "DILBERT", 
    WC_S_EMPLOYEE_ROLES_D[ACTIVE_FLAG]="Y"
    ) 

-- OFFICE CODES ~ #1 ~
VAR OFFICE_20CODE = CALCULATETABLE(
    DISTINCT(W_HIERARCHY_D_MV_OFFICE[HIER20_CODE]),
    CONTAINSROW(OFFICE_VALUES, W_HIERARCHY_D_MV_OFFICE[HIER3_CODE])
)

-- TEAM CODES ~ #2 ~
VAR TEAM_20CODE = CALCULATETABLE(
    DISTINCT(W_HIERARCHY_D_MV_TEAM[HIER20_CODE]),
    CONTAINSROW(TEAM_VALUES, W_HIERARCHY_D_MV_TEAM[HIER_CODE])
)

-- SECURE EMPLOYEE TABLE BY FILTERING
EVALUATE
(

    CALCULATETABLE(
    W_EMPLOYEE_D,
    CONTAINSROW(TEAM_20CODE, W_EMPLOYEE_D[WC_TEAM_CODE]),
    CONTAINSROW(OFFICE_20CODE, W_EMPLOYEE_D[WC_OFFICE_CODE])
    )
)