Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Role Level Security on Multiple Tables

I want to set up role level filters using 2 different tables. The default filter is to make this an 'and' filter, meaning I only want the cross sections from these two tables. I need the filters to be an 'or' filter, meaning that I need all from both sections.


For example, say I have a sales team table and a location table. I want to view all of John Doe's sales and all of US Sales in the same report. Right now, I can only figure out how to see John Doe's sales in the US.


I believe we were overthing this. I accidentally posted this same question twice, but the solution is on the other one. I simply put a filter on the sales table for the Employee ID and Location Code.

View solution in original post

Helper II
Helper II



  • Try something like this.  Create a table with the users and country combination2017-06-13_22h28_02.png



  • The set BI directional filters to your dimension tables, user and country


  • 2017-06-13_22h21_57.png
  • In your manage role, put your security filter on the bridge table



 By filtering the bridge table, you will propogate filter using the bi-directional relationships to your other 2 dimensions.   This should give you the OR clause that you are looking for.  


Remember that you need to include for John Doe a list of all the countries that he should have access to.  Not just the US.


Let me know how you get on.



This is a great creative solution, however this does not work for me. My hierearchies currently have a relationship with a common table that has sales. Power BI will not let me create a full circle of relationships between tables due to abiguity in filtering. Please see my sample data below.


CountryCityLocation ID
U.S.Ney YorkA


Sales ManagerSales PersonEmployee ID
John DoeJane Doe1
John DoeJanet Jackson2
John DoeTito Jackson3
Rick RossNick LaChey4
Rick RossJustin Timberlake5
Rick RossSteve Austin6



Employee IDLocation IDAmount
1E $          500.00
1A $          300.00
1B $          250.00
2C $          750.00
2C $          650.00
3D $          200.00
4D $          320.00
5C $          890.00
5C $          400.00
6C $          165.00
6E $          500.00
6A $          230.00
6A $          320.00


You can still use the bridge table concept, the key it to ensure that you have the combination of all the users and countries that exist in your data set.


Remove the BI directional joins and instead use the following code against the user table and country table under manage roles:



FILTER ( SecurityBridge, [User] = USERNAME () || [Country] = "US" ),
'SecurityBridge'[User], 'User'[User]



    FILTER ( SecurityBridge, [User] = USERNAME () || [Country] = "US" ),
    'SecurityBridge'[Country], Country[Country]



This will filter the bridge table for a list of user that are either in the country or is that actual user.  You can then filter the user table appropriately and do the samething for the country table.  This should give you the correct combinations.


Let me know how you get on.




I did a CROSSJOIN with the Country and User Tables to make my bridge table. Then I copied your formulas into the role filters. This did not work for me. 


Do I need to make any relationships with the bridge table? I tried that with no success.

I have created an example using the data you supplied below.  Row Level Security Example

Hi OpenDataLab,


I opened your pbix file and viewed as John Doe , and i adjusted the filter from || to && and In security bridge table i removed canada for Joihn Doe. So i expected to see only US sales..but i still can see everythin? how come? Do I need to send some settings or so?

I believe we were overthing this. I accidentally posted this same question twice, but the solution is on the other one. I simply put a filter on the sales table for the Employee ID and Location Code.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors