Ranking while using RLS

[PoetryinMotion1]

Hey all, bit of a tricky one for me today.

I have two tables, a username/email table with unique records and a fact table; the user table filters the fact table.

I have a role in my report that limits fact table visibility based on username sign-in, and another role that allows full access without RLS.

My issues are the following:

1) On both roles, when a user is selected in a slicer coming from the fact table, I want to rank where they sit in comparison to all other users on the Fact Table based on the sum of column x. This is fine for the 'Full Access' role, but when I apply RLS, the rank is always #1 given that they're the only user in the table.

2) Similar to the first issue, I have an additional ranking I'd like to apply as follows: Some users share a value in their 'Department ID' column of the Fact Table. I'd like to rank how the selected user (either forcibly or voluntarily depending on the Role) stacks up to other users within their same Department ID. Again, I'm able to do this for the 'All Access' role without any issues, but can't seem to get the same logic when RLS is applied.

Thank you!

[lbendlin]

What's the actual issue?  This is how RLS works.  You can't have your cake and eat it too.

[PoetryinMotion1]

I'd just like to know if there's a way to achieve the desired result of ranking for all, maybe by duplicating the fact table? I understand that's how RLS works 🙂

[lbendlin · Accepted solution]

Yes, you create a duplicate of your fact table that is not impacted by RLS and has all identifiable/sensitive information removed/summarized away.