Troubleshooting slow RLS?

[Anonymous]

My report is working very very slow after I implemented RLS:

Very it worked fine, now with the below 2 RLS it is extremely slow...

How can I troubleshoot it?

I have 2 roles:

Role1: (everyone has access):

RLS

Employee table:       

=[NT Username] = USERNAME()

Projects table:

=[Key_Project] IN

SELECTCOLUMNS(

    FILTER(

        'Engagement Role'

        ,'Engagement Role'[UserName] = USERNAME()

        )

    ,"Key_project"

    ,'Engagement Role'[Key_project])

Role2: (everyone has access):

Employee table:

=[TeamLeadEmployeeID] IN

SELECTCOLUMNS(

                           FILTER(

                                                      'Employee Current'

                                                      ,'Employee Current'[NT UserName] = USERNAME()

                           )

                           ,"RLS_Ids"

                           ,[Employee Number])

Projects table:

= VAR MyEmployeeNumber=

SELECTCOLUMNS(

FILTER(

'Employee Current'

,'Employee Current'[NT Username]=USERNAME()

)

,"EmployeeNumber"

,'Employee Current'[Employee Number]

)





VAR My_Employees =

SELECTCOLUMNS(

    FILTER(

'Employee Current'

,'Employee Current'[TeamLeadEmployeeID] = MyEmployeeNumber

) ,"EmployeeNumber"

    ,'Employee Current'[Employee Number]

)





   



RETURN

[Key_project] IN

SELECTCOLUMNS(

    FILTER(

        'Engagement Role'

        ,'Engagement Role'[Employee Number] IN My_Employees

    )

    ,"Key_project"

    ,'Engagement Role'[Key_project]

)

[GilbertQ]

Hi @Anonymous 

I would suggest updating your row level security syntax to not use the in variable. This is because that can take a lot of computer time in order to calculate that. Ideally you would have a dynamic row level security where it is going to use the existing relationships to determine what user has access to what. Here is an example Power BI Desktop Dynamic security cheat sheet - Kasper On BI