Row Level Security is a cool feature available in both Power BI and SSAS tabular model. It allows you to authorize different users to different information within your organizational data. In this post, I will talk about how Row Level Security works when there is a bi-directional relationship. Lets take following example, where we have salary information of managers and sales information of cities. Sales of each city is managed by a specific manager. Check the relationship diagram to get a clear idea.
In this case, City and Manager tables have a bi-directional relationship , which means both Sales and Salary information can be filtered by a City as well as a Manager.
Now I need to create a role called “Manager1” which defines Row Level Security using Manager table. As shown in bellow image, I have defined Row Level Security mentioning that users in this role can only view data related to Manage ID =1. Please note that according to the relationship between Manager and City tables, Manager 1 over looks City 1.
Now I crate a table visualization in Power BI using this data set and this is how managers and their salary information are shown.
Lets view same set of information as “Manager1” role.
Now the table looks like this. All the users in Manager 1 role can only view salary information based on the Row Level Security we defined earlier.
Lets add sales information to the same visualization. As you can see, only sales of the particular city which is over looked by Manager 1 are shown the sales.
Lets remove “Manager Name” field from the table and add “CityName” field to it. It will show all the Cities and Sales for those cities , irrespective of the fact that only “City 1” is managed by “Manager 1”. Check the Image bellow.
However, it only shows salary information of the “Manager 1”. What has happened here is, although we have defined the relationship between Manager and City tables, Row Level Security has only applied to Manager table and Manager’s Salary information table. What we must understand here is that unless we explicitly mentioned, Power BI/ Tabular model does not pass Row Level Security via a bi-directional filter.
Lets edit the relationship between Manager and City table and this time check the “Apply security filter in both directions” check box. ( In tabular model , this show as “Row Level Security”). Check the highlighted image bellow.
Once that is done, Not only Managers, but also Cities get filtered to Manager 1 role. As you can see bellow, Manager 1 over looks City 1 and now he can only view sales of his city.
Hope this gives you how Row Level Security works when there is a bi-directional filter in Power BI/ Tabular model. Thanks for reading and Cheers !!!.