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.