Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-xulin-mstf
Community Support
Community Support

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
Not applicable

Thank you very much. That works perfectly.

selimovd
Super User
Super User

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors