Help on RLS Setup

[Kiruthiga]

Hello Team,

I appreciate your time! I am new to PBI and looking for some help on setting up the RLS for my PBI data model. 

I have a table called Fact_internal with columns  selling_vendor ,pg,segment,sales and so on.

I have another table called Fact_Vendor with the same columns  as Fact_internal along with some additional logic added.

I have the RLS_security table with vendor_profile,external_id,vendor_num & Selling_vendor fields.

Currently RLS_Security table is connected to both Fact_Vendor and Fact_Internal tables.

I am currenly using RLS_Profiles[EXTERNAL_ID] == USERPRINCIPALNAME() 

which is good but however it is retriving the data from both the tables( Fact_Vendor and Fact_Internal).

But when I login as a selling_Vendor then I need to get the data only from Fact_Vendor and likewise when I login as an internal user then I need to get the data only from Fact_Internal.

Appreciate your thoughts!

Thank you!

Best,

Kiruthiga

[Kiruthiga]

NVM ., got it I add the corresponding table's advance editor.,it worked., thank you again!!

[Kiruthiga]

Hi,

Thank for your time!! I could easily add UserType "internal" and "vendor" to RLS table. But do I need to add another column UserType in the respective Facts(Fact_Internal and Fact_Vendor) as well in the database?  

Because when I click on the Fact and while manage role I couldnt enter a column belongs to other table., 

for eg in this case I click on Manage Role-->Internal--->Fact_Internal-->Switch to DAX--> I could see a list of fields only from this table to  populate any condition over this table.,

I could add the UserType in the respective fact but just would like to make sure is there any other easy ways in the PBI end itself.,

thanks a lot!!

Best,

Kiruthiga

I couldnt directly enter the above expressions on the Fact table DAX expressions.,

[DataNinja777 · Accepted solution]

Hi @Kiruthiga ,

Right now, your RLS (Row-Level Security) setup is doing a half-decent job. You’re using RLS_Profiles[EXTERNAL_ID] = USERPRINCIPALNAME(), which means you're correctly limiting data by user login. But the big problem is, it's not smart enough to know whether the user should be seeing only Fact_Internal or only Fact_Vendor. It’s like letting everyone into the party but not checking if they’re supposed to be at the VIP table or the kids' table. You need RLS logic that splits them properly: if the user is a Vendor, show Fact_Vendor. If they’re an Internal user, show Fact_Internal.

The missing link is a clear flag that tells you if the user is an internal user or a vendor. Right now, it’s not obvious from your model. Ideally, your RLS_Profiles table (or related table) should have a column like UserType that says "Internal" or "Vendor". If you can add that, everything becomes a lot easier. But assuming you cannot edit the tables right now (because IT lockdown, of course), you could also hack it by inspecting the EXTERNAL_ID — for example, if it contains "@yourcompany.com" it’s an internal user.

In the actual RLS settings, you have to set up separate table filters for Fact_Internal and Fact_Vendor. That’s the key. Power BI lets you assign a different DAX filter per table in the model.

For the Fact_Internal table, you want to apply a filter that says "only for internal users." So the DAX rule for Fact_Internal would look something like this:

RLS_Profiles[EXTERNAL_ID] = USERPRINCIPALNAME()
&& SEARCH("@yourcompany.com", RLS_Profiles[EXTERNAL_ID], 1, 0) > 0

This checks if the user's email includes "@yourcompany.com". If yes, we assume they’re internal and allow access to Fact_Internal.

For the Fact_Vendor table, you want the opposite — only for vendors. The DAX rule for Fact_Vendor would be:

RLS_Profiles[EXTERNAL_ID] = USERPRINCIPALNAME()
&& SEARCH("@yourcompany.com", RLS_Profiles[EXTERNAL_ID], 1, 0) = 0

This checks if the user's email does not contain "@yourcompany.com", and only then shows Fact_Vendor data.

If you have a proper UserType column in the RLS_Profiles table instead of this email hacking workaround, then the formulas would be even cleaner. For Fact_Internal, it would simply be:

RLS_Profiles[EXTERNAL_ID] = USERPRINCIPALNAME()
&& RLS_Profiles[UserType] = "Internal"

And for Fact_Vendor:

RLS_Profiles[EXTERNAL_ID] = USERPRINCIPALNAME()
&& RLS_Profiles[UserType] = "Vendor"

That would be a lot cleaner and less prone to weird surprises when someone has an unexpected email address format.

The bottom line: Your model needs to check not just who the user is, but also what type of user they are, and apply different RLS logic to Fact_Internal versus Fact_Vendor. That way, when a vendor logs in, they only see Fact_Vendor, and when an internal person logs in, they only see Fact_Internal. No more data free-for-all.

If you want, I can also show you a slightly fancier trick that works even if a user has both roles (say, they’re a vendor contact but also part of internal finance team). It’s a bit more complicated but super solid if you're aiming for "no surprises" long-term. 

Best regards,