Dynamic Row Level Security (Rls) and Company Hierarchies

[ChrisMBarber]

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.

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

Employee Hierarchy

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:

Salaries and Commission Worked Example.pbix

[dexterzzz]

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

RETURN PATHCONTAINS(Employees[Path],UserID)

[MarkH]

PBIX does not appear to be working?

Object intsance not set to the value of an object.

[MarkH]

Ok, sorry. Old version of desktop