topic Re: Help with logic on row level security in DAX Commands and Tips

Help with logic on row level security

Anonymous — Mon, 05 Sep 2022 16:26:30 GMT

Please can someone help with a row level security query.

I have the following tables

EntityA

User

PaulSmith@A.com

JohnSmith@A.com

CarlSmith@A.com

EntityB

Name	Icon
A	Z
B	Z
C	X
D	X
F	C
ZA	C

I need to create a DAX formula to allow row level security for the following logic

If a user is not in Entity A table then the user should be restricted to see only records where Icon <> C. If the user is in Entity A then they will have access to all records in Entity B

Re: Help with logic on row level security

PaulOlding — Mon, 05 Sep 2022 19:42:39 GMT

Hi Anonymous

You could use this in the RLS filter expression for EntityB

VAR _User = USERPRINCIPALNAME() RETURN (_User IN EntityA && EntityB[Icon] <> "C") || (NOT _User IN EntityA)

Be aware that USERPRINCIPALNAME() can give you a different result in Desktop that it does in PBI Service.

Re: Help with logic on row level security

Anonymous — Tue, 06 Sep 2022 09:30:05 GMT

Thanks Paul.

Not sure if the above logic will work. I need to add the filter to the Entity B table in the Mangage Roles section.

Re: Help with logic on row level security

Anonymous — Tue, 06 Sep 2022 09:55:52 GMT

I'm receiving the following error:

The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression.

Is it because the User field is not specified in Entity A?

Re: Help with logic on row level security

PaulOlding — Tue, 06 Sep 2022 12:17:19 GMT

Anonymous

Ah, yes. Looks like it's the wrong way round.

VAR _User = USERPRINCIPALNAME() RETURN (_User IN EntityA ) || ((NOT _User IN EntityA) && EntityB[Icon] <> "C")

.. here's the results