Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
Thank you very much. That works perfectly.
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?
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()
)