Rank function with RLS applied to dataset

I have a dataset containing a list of users and their scores. I want to calculate a rank for each user. There’s RLS applied to the dataset and I want to calculate the rank for each user based on their score. The goal is to use RLS to show every user their own rank in a KPI when they view the dashboard. I’m wondering if anybody has had any success with this type of calculation in QuickSight.

The issue is that every user who views the dashboard is seeing a rank of 1. It appears that RLS is being applied before the rank is calculated. Because the dataset is reduced to 1 row only after RLS is applied, the rank is always 1.

  1. Using PRE_FILTER with the rank function doesn’t help because QuickSight doesn’t treat RLS as a filter.
  2. Doing the rank calculation in the dataset or in the analysis doesn’t change the result.
  3. I also tried to calculate the rank in a dataset without RLS, use that dataset to create a child dataset and apply RLS to the child dataset only. That didn’t work. The rank is still showing as 1 for every user.

Instead of using RLS try using dynamic default in filter options because with RLS you will be filtering the dataset in the first place. The second option could be using a custom SQL to generate rank instead of using quicksight formula.

Thanks for the suggestion. Yeah, I think I’ll have to calculate the rank in a custom SQL.

I’m always a bit paranoid about not using RLS because if there’s any mistake in creating the filters in the analysis, there’s a chance users will see data that they’re not supposed to see and for us it’s a huge security concern.

Using dynamic default is similar to using RLS and for security purpose you can disable filtering on the dashboard which you are publishing for your agents. Or if you have to publish a dashboard go ahead with custom SQL that would be more prominent method and you can apply row level security in that.