Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table A like following for Row Level Security (RLS) set up:
UserID AccountID
001 1
001 2
001 3
002 3
002 4
002 5
I have a few tables (say B1, B2 & B3) in the main data model for dashbaord, all with AccountID column. What I need for RLS is user 001 can only see accounts 1,2,3,and user 002 only see accounts 3,4,5. Because AccountIDs are not unique, table A is disconnected from main data model - if I l link AccountID in table A to the data model, it will change the filter direction to "both" and breaks RLS (each user will see all data).
Now, my question is , can I create a measure "Flag" saying:
=1 if AccountIDs in B1 or B2 or B3 are in the AccountIDs in table A
=0 if AccountIDs in B1 or B2 or B3 are not in the AccountIDs in table A
In this way, I can put a page filter using "Flag=1" and each user will only see their own data. Adding a physical column as a flag to each data table B1-B3 won't work for RLS, because when user 001 log in, the whole dashboard should only use data from accounts 1,2,3, not 4 and 5.
For measures on the dashboard, I can add filter in DAX code to filter out accounts I don't need (like 'table B1'[AccountID] in
VALUES('table A'[AccountID]), but my dashboard has some data tables, not all measures. So I have to pick up only those accounts
each user can see for the dashboard.
Thanks,
YL
@ylin88_waters , Based on what I got so far, Your RLS table - Table A, need to an have email and should join the with other 3 table, many to many is fine. It should filter the other tables
After that, you can use a role where you can have
email = userprincipalname()
Power BI- Row Level Security: Handle ALL, UserPrincipalName: https://youtu.be/KVLEnIUo4pc
amitchandak:
Thanks for the information. My Table A has an email. But if it's in "both" direction when Talbe A links to other tables by AccountID, it's easy to break the RLS and users can see all data. It may be fine sometimes, depending on the data model. But I had that problem many times. It seems the only way to gurantee the RLS always works is you filter data in one direction, from Table A to Tabel B.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |