Implement both RLS and OLS together in Power BI

In this writing, I want to share how I implemented both RLS and OLS together in my Power BI report.

Most of my Power BI reports, I usually implement dynamic RLS. And the below describes steps how I implement RLS.

1. In Report tab, click "Manage roles" under Modeling menu. Or, In Model tab (like the below figure), click "Manage roles" under Home menu.

2. Click "Create" button and create Roles.

3. I named it RLS hierachy.

4. Select a table and a column that the security has to be implemented.

5. Write DAX formula that results True or False.

6. Click "Save".

- in the below picture, "Noshow Salary" is for OLS, so you can ignore this until I describe about OLS.

And test the implementation whether it works correctly.

1. Under Modeling menu, click "View as".

2. Check the role name that you created.

3. Check "Other user", and enter one of the userprincipalname that you want to test & check.

4. Click "OK"

Once the test is successfully finished, go to Power BI Service, and find the dataset.

1. Click three dots.

2. Click "Security".

And, add users by entering each user's userprincipalname (or user group name), which is usually an email address (or a group name). And then, click "Save". Once saved, you can hover over on Number 3 area in the below figure, and then three dots will show up. Click the three dots and you can see "Test as role" button. You can test it and finalize the implementation.

Like above, only implementing dynamic RLS is not that difficult.

But when you try to implement OLS together with RLS, please follow the below.

When creating OLS on top of the above RLS,

1. Inside "Manage roles" section, click "Create" in order to create a new role.

2. Name a new role. I named it as "Noshow Salary", because I wanted to restrict some users to see a salary column in a HR table.

The below is usually not properly implemented, and most people makes mistake. Be sure not to skip the below.

3. Check the table name that is the same as what you checked when implementing RLS.

4. Write the same DAX formula as what is written in RLS.

5. Click "Save".

Go to External Tools menu ➡ Tabular Editor.

1. Find a table and a column that you want to hide. In my sample, I wanted to hide [Base Salary] column in HR table for some users not to see this. By implementing this, users who cannot see this column also cannot see measures that are using this column.

2. Select the role that you created for implementing OLS.

3. Select "None"

4. Save Tabular Editor.

Now, you have two roles. In my case, I have "RLS hierachy" role and "Noshow Salary" role.

After publishing it to Power BI Service, when implementing the two roles in the dataset, keep in mind that only enter the person's userprincipalname who are restricted to see [Base Salary] column into the "Noshow Salary" role. In my case, I entered two people like below.

And also keep in mind that these two people's userprincipalnames were not entered into "RLS hierachy" role. Because those are different role and one person cannot be under two different role.

And, click "Save"

To summarize, when implementing RLS and OLS together,

- Make sure that one user cannot be under two different roles at the same time.

- This is the reason why I have to write the same DAX formula with RLS, when creating a new role for OLS.

- Under the role of OLS, DAX formula is meant to be applying True or False for each row in the table, and editing Tabular Editor is meant to be selecting which column in which table to hide.

I hope this helps having fun in implementing RLS and OLS together in one data model.