Here is another interesting use case of Row Level Security (RLS). It’s funny that my first post was also about RLS – when you use RLS, lot of different challenges show up (which are not relevant and do not have any use cases without RLS).
In this post we are going to look at how we can implement RLS based on a certain business logic/condition.
Basics of Row Level Security (aka RLS)
Just a quick note on Row Level Security: it is essentially a technique to filter rows based on the user who is viewing the report. Here is a typical example: a Sales table contains data for all company sales with salesperson information. A single Power BI report is viewed by all the salespeople but we want each salesperson to see only their own sales. This is where RLS comes into the picture.
Based on the salesperson who is currently viewing the report, the data for other salespeople gets filtered out even though the dataset contains data for all the salespeople.
Parts of RLS
Who is the current user: Identify which user is currently logged in. Luckily, we have a DAX function called USERNAME() which returns the current user login name. Details of this function are provided in the link at the end of this post.
RLS role: It is basically a DAX expression that uses business logic to filter the rows from the user; it returns either TRUE() or FALSE(). The rows that return FALSE() value are hidden from the user.
Problem Statement
So now we know the basics of RLS, how it works, and what the key components are to successfully implement RLS.
We have the following table that contains Name and Room Size for each user. The problem we are trying to solve here is that based on the current user logged in, we want to take the room size of that user and show all the rows that are between -20% to +20% of the current user’s room size.
Here is the sample data:
Based on the above table, if user “A” is logged in we want to show all the rows where the room size is +/- 20% of the current user’s room size. User “A’s” room size is 20 and the range is going to be 16 to 24 (the lower range is 20 x 80% and the upper range is 20 x 120%). This means that we want to show all the rows where the room size is between 16 and 24, and as per our sample table we expect to see following rows when user “A” is logged in.
In the case where user “C” is looking at the report, we will see the following rows (range 80 to 120)
Solution
To implement this, we need the following measures:
- Current user’s room size
- Lower Range
- Upper Range
In RLS role, return TRUE() if the room size falls between the lower and upper range – otherwise return FALSE() (hide rows from the user).
Quick list of steps to create a new role:
- Go to “Modelling” tab
- Click “Manage roles”
- Click “Create”
- Enter role name
- Select the table on which the business rule is going to be applied
- Enter role formula
- Click “Save”
Here is the DAX expression which will do the magic for us:
--get current user name
VAR __user = USERNAME()
--get current user's room size
VAR __roomSize =
CALCULATE (
[Room Size],
ALL ( 'Table' ),
'Table'[Name] = __user
)
--get range
VAR __upperRange = __roomSize * 1.20
VAR __lowerRange = __roomSize * 0.80
--check room size in the range, return true or false
VAR __rls =
[Room Size] >= __lowerRange &&
[Room Size] <= __upperRange
--final return
RETURN __rls
Let’s see how it works when we are viewing as user “A” or “C.”
I hope this post is helpful and provides some insight on how you can implement your business logic in RLS.
Your valuable and constructive feedback is always welcome and I am always open to learning from your interaction with my posts!
Credit
I would like to thank my daughter for helping me with this post. She is currently a third-year BCom student at UBC Sauder.
