Forum Discussion

Frequent Visitor

2 years ago

Solved

RLS implementation on PowerBI Desktop (sources are Datamart and Excel)

We are connecting PBI desktop with 2 sources , Tractional tables from Datamart and Security table from other like (Sql,Excel). we have joins between the tables (Security filters Student). Implemented RLS on top of Security at Report level. it is not working as expected.

For Example- Security table having Extracurricular activities and Datamart source Student having Extracurricular activities, we build relationships between these 2. having RLS DAX on User email =UserPrincipalName(), Data is not filterin in visual having only student information

Anonymous
2 years ago
Hi, Girija0203

If you use the datamart import connection mode to apply RLS normally, this is what RLS can support:

Here are the results

But judging from the screenshot of your relationship, you are using Direct query mode, which forms a composite mode with your Excel sheet in Import mode:

In composite mode, RLS does not take effect, as mentioned in the documentation:

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

In this case, you have two options:
1. Set up RLS in datamart.For this part, you can refer to the document below

Control access to datamarts (preview) - Power BI | Microsoft Learn

2. Connect to your datamart using import mode in Power BI desktop

Best Regards

Jianpeng Li

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

4 Replies

Anonymous
Not applicable
2 years ago
Your ideas is so great 123abc

Hi, Girija0203

Based on your description, I've created the following two tables and demonstrated the process of creating a proper RLS with them:

The relationship between them is as follows:

Create an RLS for table1:

Test if RLS takes effect in Power BI desktop:

Check whether the view takes effect:

Apply this RLS for your email in the Power BI service:

I've uploaded the PBIX file for this test below.

Best Regards

Jianpeng Li

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

RLS.pbix21 KB
- Girija0203
  Frequent Visitor
  2 years ago
  Model is half from datamart and half from import, RLS is not filtering data
  
  in above example StudentData from Datamart and Security from Import mode in PBI desktop
  joins are like below
  And RLS has been
  When view as
  RLS is not effecting on
  
  Regards,
  Girija
  - Anonymous
    Not applicable
    2 years ago
    Hi, Girija0203
    
    If you use the datamart import connection mode to apply RLS normally, this is what RLS can support:
    
    Here are the results
    
    But judging from the screenshot of your relationship, you are using Direct query mode, which forms a composite mode with your Excel sheet in Import mode:
    
    In composite mode, RLS does not take effect, as mentioned in the documentation:
    
    Use composite models in Power BI Desktop - Power BI | Microsoft Learn
    
    In this case, you have two options:
    1. Set up RLS in datamart.For this part, you can refer to the document below
    
    Control access to datamarts (preview) - Power BI | Microsoft Learn
    
    2. Connect to your datamart using import mode in Power BI desktop
    
    Best Regards
    
    Jianpeng Li
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
123abc
Community Champion
2 years ago
Following links will help you:

https://learn.microsoft.com/en-us/training/modules/row-level-security-power-bi/

https://www.youtube.com/watch?v=ZJzCkxv5wxg

https://www.youtube.com/watch?v=fh8HpSNNP30

How to Set up Row Level Security in Power BI - Zebra BI

Power BI and Excel; More than just an Integration - RADACAD

AND

Here are a few steps and considerations that might help resolve the issue:
Check Relationships: Ensure that the relationships between your tables are correctly defined. The relationship between the Security table and the Student table should be properly set up to allow filtering.
RLS DAX Expression: Verify that your DAX expression for RLS is correctly implemented. The expression UserPrincipalName() should match the email format in your Security table. For example:
[UserEmail] = USERPRINCIPALNAME()
Role Assignment: Make sure that the roles are correctly assigned to the users in Power BI Service. After defining the roles in Power BI Desktop, you need to publish the report to Power BI Service and assign the roles to the respective users.
Testing RLS: Use the “View as” feature in Power BI Desktop to test the RLS roles. This allows you to see the report as a specific user and verify if the data is being filtered correctly.
Data Refresh: Ensure that the data is refreshed and up-to-date in both the Datamart and Excel sources. Sometimes, outdated data can cause issues with RLS.
Security Table Configuration: Double-check the configuration of your Security table. Ensure that it contains the correct user emails and the corresponding extracurricular activities.
Power BI Service Settings: In Power BI Service, ensure that the dataset settings are configured to apply RLS. Sometimes, RLS might not work as expected if the settings are not correctly configured.