RLS for dynamic Page buttons

[abayless]

I have two tables. The first, Personnel, looks like this. There is also a measure named [Page Role] that returns the Page View for the current user.

Email	Page View
joe@mycomp.com	EP
sue@mycomp.com	IP
frank@mycomp.com	Corp

The next table, Page Sec, looks like this

Page Name	Visible for EP	Visible for IP	Visible for Corp
EP	1		1
IP		1	1
O&R	1	1	1

Basically, I'd like to use the RLS to limit the Page Sec table to only show Page Name when the appropriate column =1. The column I'd use depends on the 'Personnel'[Page View] (or [Page Role] measure) to define which column to use. For example, the Page View for Joe is "EP". So when Joe is the user, I want the Page Sec table to filter so only Visible for EP = 1 is showing.

Joes View of Page Sec Table:

Page Name	Visible for EP	Visible for IP	Visible for Corp
EP	1		1
O&R	1	1	1

So this way, a drop-down using Page Name would only show (EP, O&R) as options for Joe. If Sue looked she'd only see rows for "IP" and "O&R", etc.

I've never built a complex RLS expression before, so I'm not sure how to accomplish this. Here's my attempt

calculate(
  var ViewType = [Page Role]
    return
     switch(true(),
           ViewType = "EP",[Visible for EP]=1,
           ViewType = "IP",[Visible for IP]=1,
           [Visible for Corp]=1)
)

When I put this in the Roles for the Page Sec table, I get the error "Column 'Visible for EP' cannot be found or may not be used in this expression". Any ideas of how I can make this happen?

[v-yueyunzh-msft · Accepted solution]

Hi , @abayless 

Here are the steps you can refer to :

(1)First , we do not need to create relationship between two tables:

(2)We can create a role in "Manage roles":

In "User" Table we enter this dax :

[Email] =  USERPRINCIPALNAME()

In "Page Sec", we enter this dax:

var _user =USERPRINCIPALNAME()
var _Page =MAXX( FILTER('User','User'[Email]=_user) , [Page View])
var _page_names ={_Page,"O&R"}
return
[Page Name] in _page_names

(3)Then we can meet your need , the result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly