Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need help to understand RLS filters

Hi Experts, I'm using Direct Query mode with SQL Server in my reports. There is Big Data in future. I'm working on APP's owns data. I've been applied RLS based built-in a function where email = USER...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks

    I can't install SQL Profiler on the organizational system.
    Simply answer my question

    How Power BI RLS filters apply to tables? 

    Is it 1st load into the model and then do RLS filter?

    or

    Does it send a filtered query to SQL Server?

    • sturlaws's avatar
      sturlaws
      Resident Rockstar
      6 years ago

      Hi Anonymous 

       

      I ran a test on my local installation of SQL Server with direct query and RLS, running SQL Profiler and this is the query sent from Power BI Desktop to the SQL Server:

       

      SELECT 
TOP (501) [t0].[EmailAddress]
FROM 
(
(select [$Table].[BusinessEntityID] as [BusinessEntityID],
    [$Table].[EmailAddressID] as [EmailAddressID],
    [$Table].[EmailAddress] as [EmailAddress],
    [$Table].[rowguid] as [rowguid],
    [$Table].[ModifiedDate] as [ModifiedDate]
from [Person].[EmailAddress] as [$Table])
)
 AS [t0]
WHERE 
([t0].[EmailAddress] = N'[email protected]')

GROUP BY [t0].[EmailAddress]
ORDER BY [t0].[EmailAddress]
ASC

       

       

      The role was set up to filter on email address and I viewed the report as [email protected]. So with this setup, the filtering is done at the server. I get the same result when testing from Power BI Service. 

       

      Cheers,
      Sturla

      If this post helps, then please consider Accepting it as the solution. Kudos are nice too.