Forum Discussion

abayless's avatar
abayless
Advocate II
3 years ago
Solved

RLS for dynamic Page buttons

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.

 

EmailPage View
[email protected]EP
[email protected]IP
[email protected]

Corp

 

The next table, Page Sec, looks like this

 

Page NameVisible for EPVisible for IPVisible for Corp
EP1 1
IP 11
O&R111

 

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 NameVisible for EPVisible for IPVisible for Corp
EP1 1
O&R111

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?

Data Protection
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

1 Reply

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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