Tuesday, February 14, 2023

Dynamic Row Level Security for Manager Level View in Power BI

Dynamic Row Level Security for Manager and Employee View in Power BI
Scenario:
Lets suppose we have a Dynamic RLS(Row Level Security) requirement as per below.

If an Employee is a Manager ( where [Is Manager] flag =1) then user should see all the data from the Sales table.
If an Employee is  not a Manager ( where [Is Manager] flag =0) then user should see only the respective records from the Sales table.

The Data Model and sample tables as per below:

ds_Employee:

ds_Sales:


Now we can implement the Dynamic RLS for the above Scenario, on the table ds_Employee , using the following DAX expression :

IF(

MAXX(
    FILTER(ds_Employee, 
          ds_Employee[Emp User ID] = USERPRINCIPALNAME()
        ),

    ds_Employee[Is Manager]

    )=0,

    ds_Employee[Emp User ID] = USERPRINCIPALNAME(),

    1=1
)


1) Non-Manager View ( [Is Manager] = 0 ) :

2) Manager View ( [Is Manager] = 1 ) :


Notes:
In a real-time implementation, we can use the [Emp Email ID] = USERPRINCIPALNAME() instead of using the [Emp User Id]

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.