Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with logic on row level security

Please can someone help with a row level security query. 

I have the following tables

EntityA

User

[email protected]

[email protected]

[email protected]

 

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

.

  • PaulOlding's avatar
    PaulOlding
    4 years ago

    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

     

     

4 Replies

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

      • PaulOlding's avatar
        PaulOlding
        Icon for Solution Sage rankSolution Sage
        4 years ago

        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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.