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
ylin88_waters
Helper I
Helper I

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

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

2 REPLIES 2
amitchandak
Super User
Super User

@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. 

 

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.