Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

RLS - one USERNAME() with access to multiple values in the same column

Hi everyone,

 

I have a case like this

 

User 1Department 1
User 1Department 2
User 2Department 1

 

The data can also be structured like this

 

User 1Department 1, Department 2
User 2Department 1

 

I would like to setup RLS that User 1 can have access to both Deparment 1 and 2 but the Lookupvalue function only supports a single value result.

 

What should I do? Thank you so much in advance.

Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous,

     

    You can try this:

    VAR A =
    CALCULATETABLE (
        VALUES ( Table[Department] ),
        FILTER ( Table, Table[Username] = USERNAME () )
    )
RETURN
    [Department] IN A

     

    Best Regards,

    Link

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous,

     

    You can try this:

    VAR A =
    CALCULATETABLE (
        VALUES ( Table[Department] ),
        FILTER ( Table, Table[Username] = USERNAME () )
    )
RETURN
    [Department] IN A

     

    Best Regards,

    Link

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you very much. That works perfectly.

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    usually you would just filter the table to User = USERPRINCIPLENAME().

    Then the report is filtered automatically to the departments the user has access to.

     

    Would that work for your case?

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Denis,

       

      Thank you for your reply. It didn't work as the lookup function only allow one single value as result. I actually tried this:

       

      [Deparment] IN CALCULATETABLE (
      VALUES (Table1[Department]),
      Table1[User]=USERNAME()
      )