How does Row Level Security works when there is a BI-Directional Filter in Power BI/Tabular Model?

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.

image

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.

image

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.

image

Now I crate a table visualization in Power BI using this data set and this is how managers and their salary information are shown.

image

Lets view same set of information as “Manager1” role.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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

One thought on “How does Row Level Security works when there is a BI-Directional Filter in Power BI/Tabular Model?

  1. Johan Falk June 26, 2021 / 5:15 pm

    Thank you for a great explanation. I checked the box and it worked and could leave it at that, but this also gives an explanation why it worked. 😀

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s