RLS but need to compare at Region and National Level

[Ritesh_Air]

Hi,

So I have Retailer data and row level security defined which works fine. When our sales guy logs in, they see their Retailers, not any one elses.

But now, I want to compare this one Retailer to Region (which has multiple Retailers) and to National (which has multiple Regions).

But RLS restricts me to see anything beyond my assigned Retailer when I log in as (View As). How do I resolve this convoluted situation?

Thanks in Advance,

Ritesh

[v-shex-msft]

Hi @Ritesh_Air,

The most simple solution I think is duplicate query tables at the query editor side and processed on them to keep some general values/information then you can use to compare with records that filtered by RLS. (Notice: this copied summary table required not to have relationships to other tables or it will be filtered by RLS effect through relationships)
Regards,

Xiaoxin Sheng

[Ritesh_Air]

Thanks @v-shex-msft @amitchandak 

I am able to create a summary table just with National number. As national number won't change for any retailer, I can have a card next to Retailer number.

It gets little interesting when I want to compare it to Regional number. How do I force it to have just their own Region numbers? With the below formula, if I un-comment Line 4 then I can get all the Regions. But then I just want to show Retailers their own region, not all the regions.

Here is National:

Thanks,

Ritesh

[v-shex-msft · Accepted solution]

Hi @Ritesh_Air,

Perhaps you can reference the raw table region field to link this table, then records will be filtered by the current RLS filter.

How did these table relationships mapping? Can you please share some of your data structure to help us clarify your scenarios?

Regards,

Xiaoxin Sheng

[Ritesh_Air]

@v-shex-msft 

Yes, that did the trick.

thanks,

ritesh

[amitchandak]

@Ritesh_Air , I think for this you might have to create a summary table and use that.