Forum Discussion
Role Level Security on Multiple Tables
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.
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.
| Country | City | Location ID |
| U.S. | Ney York | A |
| U.S. | Atlanta | B |
| U.S. | Chicago | C |
| Canada | Toronto | D |
| Canada | Ottawa | E |
| Sales Manager | Sales Person | Employee ID |
| John Doe | Jane Doe | 1 |
| John Doe | Janet Jackson | 2 |
| John Doe | Tito Jackson | 3 |
| Rick Ross | Nick LaChey | 4 |
| Rick Ross | Justin Timberlake | 5 |
| Rick Ross | Steve Austin | 6 |
| Employee ID | Location ID | Amount |
| 1 | E | $ 500.00 |
| 1 | A | $ 300.00 |
| 1 | B | $ 250.00 |
| 2 | C | $ 750.00 |
| 2 | C | $ 650.00 |
| 3 | D | $ 200.00 |
| 4 | D | $ 320.00 |
| 5 | C | $ 890.00 |
| 5 | C | $ 400.00 |
| 6 | C | $ 165.00 |
| 6 | E | $ 500.00 |
| 6 | A | $ 230.00 |
| 6 | A | $ 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:
User:
CONTAINS (
FILTER ( SecurityBridge, [User] = USERNAME () || [Country] = "US" ),
'SecurityBridge'[User], 'User'[User]
)
Country
CONTAINS (
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 have created an example using the data you supplied below. Row Level Security Example
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.
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 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.
