cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic Row Level Security (Rls) and Company Hierarchies

The Accounting and Finance community deals with high volumes of sensitive and confidential information.  As such, reporting which enables everyone within an organisation to see only the information they are authorised to see is of importance to this community.  Leveraging the Row Level Security and the hierarchical path enables the finance team to control that one version of the truth, but limit that version of the truth depending on who logs in.

Capture.PNG

 

Key benefits of this are:

  1. Time saving - Maintenance of one set of data 
  2. Scale - The model is flexible as more individuals get added to the organisation 
  3. Secure - Leverages the current Microsoft infrastructure to maintain security by using the username of the logged in individual
  4. Transparent - One set of data means that those who have the highest level of security can see exactly what everyone sees in the same report

 

Key uses of this are when reports are produced for the company hierarchy or employee hierarchy: 

 

Company Hierarchy

Capture2.PNG

 

Employee Hierarchy

 

Capture 3.PNG

 

The files attached provides a walkthrough guide on setting up Row Level Security (RLS) and hierarchy for the Employee hierarchy above to produce the below report:

 

Screenshot of report.png

 

 

 

Comments

VAR UserID = LOOKUPVALUE ( Employees[EMP ID], Employees[User Name], USERPRINCIPALNAME () )

RETURN PATHCONTAINS(Employees[Path],UserID)

PBIX does not appear to be working?

Object intsance not set to the value of an object.

Ok, sorry. Old version of desktop