topic Re: Toggle Row Level Securiy in Developer

Toggle Row Level Securiy

freddoune — Tue, 01 Apr 2025 17:16:06 GMT

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

Re: Toggle Row Level Securiy

lbendlin — Tue, 01 Apr 2025 18:00:54 GMT

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

Re: Toggle Row Level Securiy

Deku — Tue, 01 Apr 2025 18:16:12 GMT

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")

Re: Toggle Row Level Securiy

freddoune — Thu, 03 Apr 2025 08:03:56 GMT

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

Re: Toggle Row Level Securiy

v-prasare — Thu, 10 Apr 2025 11:35:32 GMT

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

Re: Toggle Row Level Securiy

v-prasare — Tue, 15 Apr 2025 09:57:25 GMT

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

Re: Toggle Row Level Securiy

v-prasare — Mon, 21 Apr 2025 10:41:32 GMT

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