Power BI Security: Dynamic Combined RLS & CLS for Solid & Dot Line Organizational Hierarchies
Two years ago I have shared a video regarding a method on how to use DAX formulas to create the Combined Row level Security and Column level Security solution without external tool for an organization hierarchy which has mixed solid line managers and Dot line managers. If you have no chance to watch the videos please refer to the links in this video descriptions.
In that shared power BI report, due to the inflexible DAX formula to calculate the dot line manager path in the fact table “Employee Hierarchy Table”, the solution has limitations to handle more than one dot line managers parallelly in organization hierarchy, such scenarios In my practices could be that we have the different types of Dot line managers in the organization hierarchy, for example Financial Controllers and HR managers etc. they might be in same or even different layers to backup each other, and further the FC can view all sales and cost related figures under his/her layer but not above, HR can view sales but not cost under his/her layer. To overcome the limitation on handling more than one dot line managers parallelly in organization hierarchy I have modified the previous relevant DAX formulas and shared the enhanced solution in this video. In the solution we have simply applied the DAX functions CONTAINSSTRING and CONCATENTEX to mapped out the Lineage Hierarchy Paths for all involved Dot line manager IDs, with the same way as before to filter out if a input user email address is in the dot line lineage hierarchy path or not to realize the combined RLS & CLS access control. The solution is more dynamic than previous one without the restrictions regarding the digital number of employee ID, the number of hierarchy layers and if any more than one dot line manager in same line level.
References:
Power BI Security: Combined RLS&CLS for the Mixed Solid and Dot Line Organizational Hierarches (youtube.com): • Power BI Security: Combined RLS&CLS f...
Power BI Security: Combined Column Level Security & Row Level Security for Organizational Hierarchy (youtube.com): • Power BI Security: Combined Column Le...
Power BI Row Level Security for Organizatioal Hierarchy (youtube.com): • Power BI Row Level Security for Organ...
