cancel
Showing results for 
Search instead for 
Did you mean: 
ibarrau

Page level security with RLS

The first and most important thing we need to understand to get our expected result is forcing the user to use a custom navigation inside of the report. Let's avoid the default pages navigation. We might need to build a single visible Menu page to start our report. You can find a lot of post and videos talking about buttons approaches to build menu. I'll leave only one example, but you can google any other one. E.g https://blog.ladataweb.com.ar/post/187164247999/powerbi-bookmarks-episodio-3

The key feature here it's using Page Navigation for the buttons with the FX option. The FX will let us use a measure that will handle a logic to navigate depending on users roles.

A little of theory context. If we add a measure in the custom function (FX) in page navigation the engine will check if the result of the measure is a text string that matches with the name of a page in our report. The string must match exactly (key sensitive) otherwise the measure won't work.

Model

In a similar way on how dynamic RLS is handled, let's build a table that is not related with the model. The table can be managed by an excel stored in a drive or sharepoint. The table contains two columns. The user email from Azure AD and the exact name of the page they should see. You can add a row for each page a user can see.

ibarrau_0-1634664616603.png


As you can see it's not just about adding a person to each page. That's only for restrictions. We can have people that will see everything. The word "Todo" means "All".

After loading the table, we can create a simple RLS role:

ibarrau_1-1628708790843.png

 

DAX

Now that we have our model built, let's create the DAX measures for each page. Yes, you need a DAX measure for each page on the report. You are building a menu with buttons to navigate to a page, so it's a measure for each page to do it.

Let's see an example for the page "Error in Totals" in the report:

 

 

 

 

Nav_ErrorInTotals =
VAR __nav = OR(
    "Error in Totals" IN VALUES (UserByPage[PagePermission])
    , "Todo" IN VALUES (UserByPage[PagePermission])
)
RETURN
IF ( __nav, "Error in Totals", "Denegado")

 

 

 

 

We start with two conditions. If one of those is ok, then the text will return the exact string that matches the page. The first one is checking if the text "Error in Totals" is in the list of rows filtered by RLS. The second one is checking if it is an admin (remember we used a row with the page name = "Todo" for admins). The variable will return TRUE() if our table's data can find that page for the user in RLS. The RETURN with the IF to make it cleaner. The false statement in the if could be blank. The blank avoids the user navigation. The button won't do anything. However, we can improve the user experience creating a hidden page for all false statements like "Permission Denied" page or something like that (in my case the page name is Denegado). That way the user is not confused because the button will always navigate somewhere. We can even make it nicer giving information of which pages or buttons of the menu the user can access.

ibarrau_2-1628708821251.png

In order to make the navigation work we need to open de action menu in the format pane of the button.

ibarrau_3-1628708845493.png

Add the measure as the FX destination and boom! we are done. Let's see how this would work with RLS:

ibarrau_4-1628708863763.gif

Now we have built an approach restricting pages for a logged user with RLS with an external table that can be managed by business users instead of a Power Bi developer. You are also maintaining only one report instead of different copies.

I hope this helps you open your mind to find more and amazing creative solutions like this one that could be improved a lot if we hide and show buttons regarding permissions or anything you want.

Original post from ladataweb.

 

Comments
Polls
What is your favorite Power BI Feature release this month?