Implementing row-level security in Power BI is a recurring task for developers. We use several techniques to do this.. 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 by implementing RLS roles, which contain access logic to control access to data.
This logic is defined by DAX expressions and can be very simple and even very complex.
Since I already wrote a few articles on this topic here on Medium, I decided to compile the different methods into one guide instead of having different places where you have to look for information.
In the end, I'll look at them side by side and recommend which is the best approach.
I will reference my other pieces if they are available. You can find links to them in the References section at the end of this article.
We have the following variants to implement RLS:
- Simple lookup table
- Using hierarchies
- Complex DAX expressions
- Bonus: Using SCD2 Dimensions
You can skip to the next section if you are familiar with creating RLS roles.
Each RLS role uses one of two basic approaches:
- Identifying the user
- Apply access logic
The first approach is based on a list of users assigned to the data they have access to.
So when you have a table with a list of users (in the form of email addresses), you can compare the current user using the USER PRIMARY NAME() function.
The following measure uses this function to display the current user:
Current User = USERPRINCIPALNAME()
Now I can add it to a card visual to get the following result:
