In the last two blogs, we covered the basic concepts of row level security such as creating roles, assigning roles, viewing roles and using DAX to implement row level security. In blog post, we will discuss the method of URL filtering to provide row level security.
But first lets catch up!
What is Row Level Security?
Row Level Security (RLS) is a feature that restricts the access to data to a specific group of users. Row level Security allows you to give different levels of access to different users for the same report.
Row level security can be created in Power BI in three ways:
Static Row Level Security
Static Row Level Security restricts the access of report to the audience by defining a logic for the roles inside the Power BI Model.
For details: https://powerbitraining.com.au/static-row-level-security-in-power-bi-power-bi-training-in-sydney-and-melbourne/
Dynamic Row Level Security
Dynamic Row Level security refers to the use of DAX functions in order to restrict the access of your audience for the same report. Dynamic security changes as per the login credentials.
For details: https://powerbitraining.com.au/dynamic-row-level-security-power-bi-training-sydney-melbourne/
Row Level Security using URL
Row Level Security appends parameters to the report URL in order to limit the access to underlying data.
Getting Started
The data model used for this exercise consists of three tables:
- Employee: This table contains details about the Employees of AMZ Consulting.
- Office: This table contains the details about the office sites of AMZ consulting.
- Sales: This table is a fact table and contains data about sales contributions made by each employee.
The Report on which you will practice Row Level Filtering shows the Employee Performance for AMZ Consulting. This is shown in the image below.
2.At the end of the link append the string ?filter=Office/City eq ‘Sydney’
Here “?filter=” is the syntax used for filtering Sydney from the field City in the Office table.
3.Paste this new URL in chrome or any other browser to access the filtered report.
This report shows the same results as with other methods of Row Level Security.
5.At the end of the link append the string ?filter=Office/City eq ‘Sydney’ and Office/City eq ‘Sydney City’
6.Paste this new URL in chrome or any other browser to access the filtered report.
Now follow the similar steps to filter our data based on the employee names.
7.Open the report in your Power BI Pro account and copy its URL in a notepad file.
8. At the end of the link append the string ?filter=Employee/Names eq ‘Ali Noorani’
9.Paste this new URL in Google Chrome or any other browser to access the filtered report.
The filtering expression can be seen in the image below.
2.Create a Calculated column named “URL using the expression in last step.
3.Change the data type of this new column “URL” to WebURL.
4.Publish this file to Power BI Pro and replace the original dataset.
5.Visualize URL in the desktop file to find a click-able URL for each employee.
As you can see, click-able URLs for row level filtering of each employee are created.
Conclusion:
Row level security is a feature in Power BI that is used to limit the access of data to certain users. The data security is provided at row levels and hence it gets its name “Row Level” security. RLS can be created in three ways: using static values, using DAX and URL filtering. In this blog post, row level security has been implemented using URL filtering technique.
Are you a data analyst and want to learn more about Power BI?
Why not sign up for Power BI training in Australia. We provide our services in the following regions:
