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

--------------------------------------------------------------------------------------------------------