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.

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:

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.