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

[Anonymous]

Hi everyone,

I have a case like this

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

The data can also be structured like this

User 1	Department 1, Department 2
User 2	Department 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.

[v-xulin-mstf · Accepted solution]

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]

Thank you very much. That works perfectly.

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

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