Forum Discussion
RLS/ OLS/ Dynamically Hiding Column Content
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!
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:
4 Replies
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:
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?
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.
