Forum Discussion
Using MAX with row level security
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
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.