Toggle Row Level Securiy

[freddoune]

Dear all,

I have an application with the following data model : 

I have 4 RLS Tables : one for the hierarchy, one for the location, one for the country and one for the headcount.

Basically the RLS tables filter the Dim Users which filter the Fact table.

What i would like to achieve is to have a slicer with the possible values {"Hierarchy", "Location", "Country", "Headcount"}

If the user selects "Hierarchy" then the RLS will only be applied on the RLS Hierarchy table (point 1/ of the snapshot of the data model) and not on the others RLS tables. So the with this selection the Fact table will be filtered only with user from the RLS Hierarchy table.

To manage this I have created my four RLS Role with specific DAX condition. I.e, for the Hierarchy role i applied the following statment :

VAR _Selected_Role = VALUES(Param_Security_Role[Role]) -- User role selection
RETURN
SWITCH(
TRUE(),
_Selected_Role = "Hierarchy",
'DIM_USER_RLS_HIERARCHY'[USER_EMAIL] = USERPRINCIPALNAME(),
'DIM_USER_RLS_HIERARCHY'[USER_EMAIL] = "N/A"
)

When i test my development with the view as i have the following error : 

An error occurred while evaluating the row-level security expression defined on the table. A table of multiple values ​​was provided when a single value was expected.

 I do not understand this error : why a table with multiple values is provided ? Which table is it ?

If someone can help me i'll appreciate.

Thanks a lot

Frédéric

[v-prasare]

Hi @freddoune, As we haven’t heard back from you, we wanted to kindly follow up to check if your issue got resolved? or do you need any additional help?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

Hi @freddoune, 

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue got resolved? or do you need any additional help?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

Hi @freddoune,

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue got resolved? or do you need any additional help?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Deku]

VAR _Selected_Role = VALUES(Param_Security_Role[Role])

This is a table, to return a single value use SELECTEDVALUE() or a aggregation like MAX()

VAR _Selected_Role = selectedvalue (Param_Security_Role[Role], "location")

[lbendlin · Accepted solution]

VAR _Selected_Role = VALUES(Param_Security_Role[Role]) -- User role selection
RETURN
_Selected_Role = "Hierarchy" && 'DIM_USER_RLS_HIERARCHY'[USER_EMAIL] = USERPRINCIPALNAME()

[freddoune]

Hi 

thanks for the reply.

For my test i tested with 2 roles with the "View as" (Role Hierarchy and Role Location)

When i select Role = Location OR Role = Hierarchy in my slicer, the Dim_Users is not filtered and then i see all the users. I need to force one specifc RLS Pattern with the Slicer Role. How can i achieve this ?

Thanks a lot

Frédéric