How to Rank Customers outside of Row Level Security, using RLS

[Shelley]

Hi All, I'm not sure if this is possible, but seems like it would be a common scenario. I am ranking all our distributor customers against each other within a Power BI report. So, for example, we may have 100 distributor territories (APR) and I want to rank each territory among the whole list. This works great when I have access to the entire dataset. However, I just discovered - even though using ALL and RANKX - when somebody with row level security who has access to three APRs, for example, looks at the ranking, it shows only their three APRs. I understand why this happens now, but is there a way around it within Power BI? I have many different data sources with rankings - from SQL server table, to Excel files in different locations. So to take the ranking back to the dataset(s) will be very time consuming.

Here's my formula (if more than one APR is selected, it says "Select one APR", otherwise, it calculates and shows the rank):

Rank YTD Order % Growth =

IF(HASONEFILTER('Bridge'[APR]),

RANKX(ALL('Bridge'[APR]), [YTD Order $ to Prior Yr (%)], , DESC),

"Select one APR")

[v-shex-msft · Accepted solution]

HI @Shelley ,

AFAIK, ALL function works with different level filters and slicer. RLS filters are filtered on data model level, you can't do calculation between filtered and unfiltered records based on RLS filter.

You can consider to duplicate a table without relationship to original table and stored full records, then you can simply do calculate with these backup records and filtered records.

Regards,

Xiaoxin Sheng