Need Help: Dynamic RLS in Direct Query shows no records

[PBI_Member_01]

Hello Power BI Community,

I am trying to implement Dynamic RLS using Direct Query and facing issues with filtering the dataset based on the USERPRINCIPALNAME. I have prepared a mock dataset to represent my actual data. Please see the screenshots below which is built on sample data and represents the tables and relationship of the actual data on hand:

RLS Table is built on Semantic layer to maintain mapping for the employee with their designated locations. An employee can have access to more than one geo locations. The DAX Calculation I am implementing on each table is as follows:

var RLS_Data = 
CALCULATETABLE(
VALUES( 'RLS Table'[Geo_Code] ),
'RLS Table'[Emp_Email] = USERPRINCIPALNAME()
)

VAR RLS_Rule = 
SWITCH(
TRUE(),
[State_Code] IN ( RLS_Data ), TRUE(), FALSE()
)
Return
RLS_Rule

State_Code will be replaced with each table's individual column i:e Area_Code, AreaDepot_Code etc.

I get 0 rows on Power BI Desktop on DirectQuery when I implement this DAX but if I create a mock data as shown above and
implement the same DAX on ImportMode, the RLS does filter.

I've tried changing relationships and modifying the logic on my actual project but have not been able to resolve this issue. Quite new to implementing this on DirectQuery so any suggestion or resolution to this problem would be highly appreciated.

Please let me know if I can add more information to this.

Best Regards

[hefirius]

I am not sure if this will help. 

It happened to me recently when I was setting up my RLS for DirectQuery models. Similarly, the RLS worked in import mode but does not work in DirectQuery.

Ensure that you have:

1. Shared the dataset with your users under Manage Permissions
2. Adding affected users into Security in Semantic Model
3. Added the users into your workspace as Viewers (this was what worked for me and i missed out. When I setup my RLS on Import Mode, my users were using APPs to access the dashboards, and I did not have to add them into my workspace as "Viewers" for RLS to be effected"

[Anonymous]

Hi @PBI_Member_01 ,

Based on the information you provided, your RLS filters properly in import mode and does not show up in DirectQuery mode.

What you need to realize is that RLS does not apply to DirectQuery with SSO enabled, you can read the following documentation for details:

Manage a SQL Server data source - Power BI | Microsoft Learn

Add or remove a gateway data source - Power BI | Microsoft Learn

Do you have security roles defined for your data source, it is recommended that you read the following documentation:

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@PBI_Member_01 , RLS Table to Region , if need make it many to many bi-directional

Region to Country 1 to Many

The area depot will not filter sales route as it creates loops . so use measure to filter slicer fo sales route

example

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

RLS should simply work , in roles

[Email] = userprincipalname() 

Power BI- Row Level Security(RLS): Handle ALL, UserPrincipalName: https://youtu.be/KVLEnIUo4pc