RLS/ OLS/ Dynamically Hiding Column Content

[aurelius13]

Long time lurker, first time poster. I have a tricky RLS(?) question. I'm trying to selectively show content from a column on a visual based on an RLS role. As an example let's use books and plots. In the report/ visual I only want the plots for an author's own books to populate BUT I want them to still see the records and data for all the other books (just not the plots). So this is what I want an "Author 1" security role to see:

Desired table visual for "Author 1" role

Book	Author	Price	Plot
Book A	Author 1	1.75	Plot A
Book B	Author 1	1.8	Plot B
Book C	Author 2	1.9
Book D	Author 2	1.5

And this is what I want the "Author 2" role to see:

Desired table visual for "Author 2" role

Book	Author	Price	Plot
Book A	Author 1	1.75
Book B	Author 1	1.8
Book C	Author 2	1.9	Plot C
Book D	Author 2	1.5	Plot D

I'm currently using tables that look like this:

f_Book

Book	Author	Price
Book A	Author 1	1.75
Book B	Author 1	1.8
Book C	Author 2	1.9
Book D	Author 2	1.5

d_Plot

Book	Plot
Book A	Plot A
Book B	Plot B
Book C	Plot C
Book D	Plot D

It feels like I should be able to filter down d_Plot with RLS then have the relationship only pull in records that are populated but I can't seem to get it to work. I might be missing something with my RLS DAX or how the relationships are set up. It's currently *:1 (Books:Plot) and I'm using

RELATED(Combined[Author]) = "Author 1"

on the Plots table. It successfully gets ride of Author 2's plots but also the prices and I can't figure out why:

Current (not desired) table visual for "Author 1" role

Book	Author	Price	Plot
Book A	Author 1	1.75	Plot A
Book B	Author 1	1.8	Plot B
Book C	Author 2
Book D	Author 2

A simple Sum(Price) still returns correctly on the total row but I want the individual prices to show up on the table visual as well. 

I've also looked into OLS a bit but don't know enough about it to know if that is where I should be looking for my solution. 

Any help and guidance would be greatly appreciated. Thanks!

[SamWiseOwl · Accepted solution]

Hi @aurelius13 

How are you applying the filter? Do you plan on using a UserName() or UserPrincipalName() or something else?

Here I've made a measure that reads the persons email address and show the row if it matches the email:

The email is in the f_book next to the Author name:

[aurelius13]

Hi @SamWiseOwl! Ideally I would use RLS roles with UserName() / UserPrincipalName() so that the logged in author only ever sees their own plots but there would also be roles for people to only ever see books by a specific author, or from a certain publisher (not included in example data), etc. The measure you provided produces the desired results for the visualization but is there a way to also restrict d_Plot so authors can't see all the plots if they access that table directly?

[Anonymous]

Hi @aurelius13 ,

If I understand you correctly, the measure written by SamWiseOwl  already fulfills your needs. You just need to not place the Plot field in the visual object, but only the measure.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[aurelius13]

The measure @SamWiseOwl suggested worked great so accepting that as the solution. I just figured out the other piece which is running RLS on the Plot table so the measure only brings in plots that "exists" for any given RLS role. Thanks both!