Using MAX with row level security

[Anonymous]

     I have a dashboard that is using Row Level Security.  I restrict the end user so that they may only see transactions affiliated with their SalesRepID.  It works well and restricts information to a given SalesRep as designed. 

     I have tables named Company, InvoiceHeader, and SalesRep.  They are connected in a Star Schema fashion.  In the Company table there is a Company ID and a measure defined as LatestInvDate = FORMAT(MAX('InvcHead'[InvoiceDate]), "DD-MMM-YYYY").  There are 4 different Companies and the visualization shows each Company with the corresponding LatestInvDate correctly.  This information is provided to the user so that they know the last invoicing date by company. 

     When I invoke the "View as Roles" the visualization using LatestInvDate changes to reflect only a subset of the LatestInvDate data for the SalesRep in my row level security.   In effect it tells the user what their respective latest invoice date for each company is instead of what the last invoice date is for the company.  I want the visualization to ignore the row level restriction and determine the latest date for each company. 

     I have tried using an ALL filter where I ended up with multiple companies but each one showed the same LatestInvDate.  I know I must be close to the answer but cannot nail it down.  Any guidance/assistance is appreciated.

[Anonymous]

@Anonymous,

Please change your measure to the following and check if you get expected result. If you still have questions, please share sample data of you tables and describe more details about how you create RLS role.

Measure = CALCULATE( FORMAT(MAX('InvcHead'[InvoiceDate]), "DD-MMM-YYYY"),ALLEXCEPT(YourTable,YourTable[Company]))

Regards,
Lydia

[Anonymous]

I was not successful using the suggestion.

I have created row level security by retrieving the user credentials from the environment using the USERNAME() function.  I have a table named SalesRepAuth which has the salesrep number, salesrep company, calculated column putting their id together in the active directory style of "[email protected]" and another calculated column in the domain style of "mydomain\username".  Then there is a key column K_CustSlsRep in the format of [Company] & " " & [SalesRepID]. I bounce the retrieved value against the SalesRepAuth table to determine who they are.  Each sales rep is supposed to only see their respective sales, and it is working great.

I have connected the SalesRepAuth table to the CustomerSalesRep table to get more information about the sales rep.. 

I have connected the CustomerSalesRep table to the Customer table.

I have connected the Customer table to the InvcHead table.  This is the table where I need to get the latest invoice date MAX[InvoiceDate] from the column [InvoiceDate], by [Company}

I created a measure in the Company table which is defined as:

               LatestInvDate = FORMAT(MAX('InvcHead'[InvoiceDate]), "DD-MMM-YYYY")

I have a table visualization that includes Company[Company] and Company[LatestInvDate].  It is showing the correct dates for each respective company in the visualization.  However, when I turn on the "View as Role" the visualization changes and does not show all companies and it reflects only sales for the "View as Role" individual.  I do not want the visualization to be subjected to the Row level security.

[Anonymous]

I have a fourth table named Customer that is attached to the InvoiceHeader.  I have a report level filter that excludes internal customers using ICCust=False.  I would want that to apply to the MAX function as well.