A measure as a flag to filter out IDs not in another column /another table?

[ylin88_waters]

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

[amitchandak]

@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

[ylin88_waters]

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.