Dynamic RLS by PATH Level. VALUES error

[olegkazanskyi]

I am trying to set up RLS by the manager's hierarchy.

Inside the table, I have created additional columns with the managers' hierarchy level.

The data looks like this:

USER

PARENT

PATH

MANAGER_1

MANAGER_2

MANAGER_3

MANAGER_4

MANAGER_5

Where manager levels 1-6 are the PATHITEM # 1 to 6.

Now I am trying to set up RLS with the next DAX:

SWITCH(TRUE(),

USERPRINCIPALNAME() in VALUES([manager_6]), [manager_6] = USERPRINCIPALNAME(),

USERPRINCIPALNAME() in VALUES([manager_5]), [manager_5] = USERPRINCIPALNAME(),

USERPRINCIPALNAME() in VALUES([manager_4]),[manager_4] = USERPRINCIPALNAME(),

USERPRINCIPALNAME() in VALUES([manager_3]),[manager_3] = USERPRINCIPALNAME(),

USERPRINCIPALNAME() in VALUES([manager_2]),[manager_2] = USERPRINCIPALNAME(),

USERPRINCIPALNAME()  in VALUES([USER]), [USER] = USERPRINCIPALNAME())

But I get an error in RLS window:

The VALUES function expects a column reference expression or a table reference expression for argument "1"

I tested a measure with the formula and I works ok.

I can't figure out what is wrong.

[olegkazanskyi · Accepted solution]

I found the workaround.

Instead of using VALUES in RLS window, I used an additional measure for filtering:

check_in = SWITCH(true(),

USERPRINCIPALNAME() in values(PBI_DS_Owners[manager_6]),7,

USERPRINCIPALNAME() in values(PBI_DS_Owners[manager_5]),6,

USERPRINCIPALNAME() in values(PBI_DS_Owners[manager_4]),5,

USERPRINCIPALNAME() in values(PBI_DS_Owners[manager_3]),4,

USERPRINCIPALNAME() in values(PBI_DS_Owners[manager_2]),3,

USERPRINCIPALNAME() in values(PBI_DS_Owners[manager_1]),2,

USERPRINCIPALNAME() in values(PBI_DS_Owners[user),1)

I hope someone will find it useful.