Implementing row-level security in Power BI is a recurring task for developers. We use various techniques to do so. Let’s look at some of them.
When we regulate data access in Power BI solutions, we must implement RLS (Row-level security).
RLS works through the implementation of RLS Roles, which contain the access logic to control data access.
This logic is defined by DAX expressions and can be very simple and all the way to very complex.
As I already wrote a few articles about this topic here on Medium, I decided to collect the different methods into one guide instead of having different places where you must search for information.
In the end, I will analyze them side-by-side and recommend which approach is the best.
I will reference my other pieces if available. You can find the links to them in the References section at the end of this piece.
We have the following variants to implement RLS:
- Simple Lookup table
- Using Hierarchies
- Complex DAX Expressions
- Bonus: Using SCD2 Dimensions
You can jump to the next section if you are familiar with building RLS roles.
Each RLS role uses one of the two basic approaches:
- Identifying the user
- Applying an access-logic
The first approach is based on a list of the users mapped to the data they have access to.
So, when you have a table with a list of users (in the form of Mail Addresses), you can compare the current user using the USERPRINCIPALNAME() function.
The following Measure uses this Function to show the current user:
Current User = USERPRINCIPALNAME()Now, I can add it to a Card Visual to get the following result:
