RLS and Calculate

[ClaudeV]

Hi guys,

I need some help with RLS and Calculate.

My data is setup as the following:

Each Location connected to a email address and each location within a Province

I have created a RLS for each Location using the email address and the USERPRINCIPALNAME() function.  But when I view as the user I cannot get the total revenue for the region to be displayed.  I then created a separate table for the revenue per region.  Everything is working as it should.  What I would like to do is insert a namecard with dynamic content to display ONLY the region total based on the user login.

What I currently have is the following:

RLS Province = CALCULATE(SUM('Sum of Totals'[Net Sales]),'Sum of Totals'[Province]="PROVINCE NAME",'Sum of Totals'[Company]="Franchise")

I have 17 Provinces and I do not want to create one for each Province

Ideally what I would like to do is make the bold underlined part of the formula dynamic to only display the sum of net sales for the province based on the user login (as per RLS)

RLS Province = CALCULATE(SUM('Sum of Totals'[Net Sales]),'Sum of Totals'[Province]="PROVINCE NAME",'Sum of Totals'[Company]="Franchise")

I want to filter between Companies ('Sum of Totals'[Company]="Franchise") as well, hence the CALCULATE measure

I want the Location Net Sales to be displayed in the three namecards (PREVIOUS-, CURRENT-, NEXT MONTH) and the SUM of net sales for the PROVINCE to be displayed in the RLS Province and OTHER Province - based on the user login (RLS)

[ClaudeV]

@DataInsights thank you.  Solution 2 worked.  I had to use solution 2 as I created relationships between the locations and that mad a mess of everything

[DataInsights]

@ClaudeV, glad to hear that!

[DataInsights · Accepted solution]

@ClaudeV,

Here are two solutions you can try. Each solution uses the following measure and RLS filter for DimLocation:

Province Net Sales = SUM ( 'Sum of Totals'[Net Sales] )

Solution 1

Create a many-to-many relationship between DimLocation and Sum of Totals. DimLocation should filter Sum of Totals.

Solution 2

There is no relationship between DimLocation and Sum of Totals. Add an RLS filter to Sum of Totals:

[Province] IN
CALCULATETABLE (
  VALUES ( DimLocation[Province] ),
  DimLocation[Email] = USERPRINCIPALNAME()
)