Forum Discussion

ClaudeV's avatar
ClaudeV
Regular Visitor
4 years ago
Solved

RLS and Calculate

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)

 

General Comment
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    4 years ago

    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()
)

     

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    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()
)

     

  • ClaudeV's avatar
    ClaudeV
    Regular Visitor
    4 years ago

    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