![55faaf71ac5e8edcd5d48e78a187eb84.jpg](https://quantinsightsnetwork.com/wp-content/uploads/2023/11/55faaf71ac5e8edcd5d48e78a187eb84-678x205.jpg)
Welcome back to this week’s edition of the Power BI
blog series. This week, we look at static row-level security (RLS) in
Power BI Desktop.
One of the
key features that distinguishes Power BI from other Business Intelligence (BI) solutions
is its ability to provide row-level security (RLS), which is protection based upon
each row of a data table. Instead of
creating a report for each of the access profiles, you can create a single
report linked to different access profiles.
Essentially, you may specify rules (e.g. Country = “Australia”)
that filter the data that a user can see within their reports. For instance, a General Manager may access data
that is available within their division only, whereas a CEO may have
unrestricted access to the entire dataset.
Power BI
essentially displays the same report to all users but restricts the data flow
through to the report based on the security settings.
You should
note that RLS may be set up for data models imported into Power BI using Power
BI Desktop. RLS may also be configured
on Direct Query datasets such as SQL Server.
Defining
Roles and Rules in Power BI Desktop – Static
In this
example, we will set up a security option that would restrict a Manager in
Australia to only be able to view data from Australia. First, we must define the security role. To do this, we need to go to the Modeling (sic)tab, and in the Security group, click on ‘Manage roles’.
![](https://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1667294738.png/55faaf71ac5e8edcd5d48e78a187eb84.jpg)
This will bring up a dialog, which will
allow us to create and define roles within our dataset. Let’s create a new role and name it ‘Australian
Managers’. We begin by clicking the
Create button.
![](https://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1667294757.png/7019fe22b99cfe09ac90b10c598c01a6.jpg)
Once we have entered a name for the role
under Roles, we can begin adding filters.
In the second column, Tables, click the ellipsis (‘…’) next to Geography,
mouse over ‘Add Filter…’ and then select [EnglishCountry]. The ‘Table filter DAX expression’ is
presented on the right-hand side, which allows us to create a DAX expression
that must evaluate to ‘True’ before the data can be accessed by this role.
[EnglishCountryRegionName]
= “Australia”
![](https://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1667294781.jpeg/a0168cf2a9dfe12f9b237b466583418e.jpg)
When you have finished creating the role, click the checkbox above the
expression box to validate it.
Afterwards, press the Save button and the filter(s) created will be applied to the specific
role(s).
Validating Roles within Power BI Desktop – Static
With our
new role created, we can check that everything has worked as intended and that the
members of the role see the correct data.
On the Modeling tab, clicking ‘View as’, which opens a pop-up window where
you can see the roles you have created. Within this window, you may tick a role to
preview what that role would see.
![](https://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1667294883.png/3f775aec1b1702a4c5727e8fdbee6150.jpg)
![](https://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1667294888.png/4b4663488e1b3fd7c6da40309142b74a.jpg)
The ‘Other
user’ option in Power BI Desktop will only display a different view if you’ve
employed dynamic security.
With the
‘Australian Managers’ role applied, the entire report is filtered to only
display data for Australia. A yellow bar
will be displayed at the top of the report, informing the user that the report
is being viewed as ‘Australian Managers’.
To stop viewing this filter, click on the ‘Stop viewing’ button on the
top right-hand side and it will return to the full view.
![](https://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1667294904.png/15e202d9cc60b27f6156c95a35cb7ff8.jpg)
It should
be emphasised that this functionality only applies to the Power BI online
services, i.e. once data has been published and distributed online then row-level
security may be employed.
There is no
security in the PBIX file; you can see the entire dataset. There is no way that you may have any
security with Power BI Desktop files and roles may only be created or modified
inside Power BI Desktop.
Next time, we will
continue by looking at static security through RLS in Power BI Service.
Check back next week for more Power BI tips
and tricks!
Be the first to comment