Forum Discussion

PBI_Member_01's avatar
PBI_Member_01
Helper III
2 years ago

Need Help: Dynamic RLS in Direct Query shows no records

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

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • hefirius's avatar
    hefirius
    Frequent Visitor
    2 years ago

    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"