Using RLS with Datasets

[dwel0120]

I have a dashboard/report that uses a pbi dataset to connect all my tables. The dataset houses about 15 tables. The dasboard will be located in a workspace and shared out to groups. RLS is required because certain groups can only view their data based on their email address. As of right now, I cannot get RLS to work properly. I have never used RLS while being connected to a dataset (I've only used RLS with dashboards that directly connect to the datasource like SQL or Snowflake). I researched and researched and cannot find any troubleshooting articles. How are you supposed to implement RLS while using a dataset?

[otravers · Accepted solution]

RLS should be defined in the parent dataset, not in the Live Connection report. If memory serves you can't use the security settings RLS test functionality with Live Connection report, you'll have to test accessing the report with one of the RLS'd users (make sure they're a Viewer in the workspace).

[otravers]

I wrote an entry that hopefully will help you understand how RLS works:

https://www.oliviertravers.com/row-level-security-rls-business-intelligence/

Can you elaborate on what you've done so far and how you're diagnosing that RLS is not working properly?

[dwel0120]

@otravers 

Description: We've added 1) the appropriate table and relationship to the dataset 2) roles to the dataset which define the rls 3) linked the dashboard to the dataset 4) defined blank roles in the dashboard. The RLS works in the dataset file itself. The RLS does not work in the dashboard which is linked to the dataset. I have tried making the user we are testing someone with build access to the dataset and testing their permissions in the service. This does give them access to the dashboard.

[otravers]

This is working as intended, only users with the Viewer workspace role have RLS applied to them. Everyone who is Member or above will see everything.

You can test what RLS does in a report from the Security settings of the dataset and preview the result in reports (but not dashboards, they're not listed there).

[dwel0120]

@otravers 
I am testing it in the desktop dashboard as view role as and it is not working.  

[otravers]

To be able to help you we need you to be as precise as possible. There's no such thing as a "desktop dashboard", dashboards only exist in the service. The PBIX file you're opening in the desktop is a report with its underlying dataset.

Can you show a screenshot of what you're doing in Power BI Desktop to test RLS, as well one of your RLS DAX expressions?

[dwel0120]

@otravers 

I have the RLS email table connected to a table in my dataset with a many to many relationship and a single direction (from rls email table to table in dataset). I set up the roles (image below) in the dataset. The RLS worked in my dataset. 

For my pbix file, I have the RLS email table connected through the pbi dataset. What would be the next steps? The table obviously doesn't show up because it is not directly connected to the PBIX file (see image below). Do I need to connect the RLS email table directly to the PBI file as well and then create the same roles and DAX expression?

[otravers]

You're describing your "dataset" and "PBIX file" as somehow two different things. Are you creating a Live Connection report in the PBIX file (i.e. you started the report with "Get Data > Power BI datasets")?

[dwel0120]

@otravers 

I tried connecting the RLS Email Table directly to the PBIX file that contains the report (and all the visuals) and creating a relationship to one of the tables that has been brought in through Get Data > Power BI datasets and that did not work either. Again, the RLS does work in the pbix file containing the dataset with all the tables but will not work in the pbix containing the visuals with the connection to the pbi dataset.  

[otravers · Accepted solution]

RLS should be defined in the parent dataset, not in the Live Connection report. If memory serves you can't use the security settings RLS test functionality with Live Connection report, you'll have to test accessing the report with one of the RLS'd users (make sure they're a Viewer in the workspace).

[ktb222]

Hi @otravers 

I have an issue with RLS, when I combine a Power BI Dataset with data from Excel in a new dataset. RLS from the first seems to be in conflict with the new RLS. The table which needs RLS in the new dataset is connected to a table with RLS in the Power BI Dataset. Works fine in PBI Desktop, but not in Service. Do you know how I can solve this?

[dwel0120]

@otravers 

So I can only test the live connection report in the workspace?  Is that correct? And there is no reason why I can't use RLS with a live connection dataset, correct? It should work?

[otravers · Accepted solution]

Yep, you'll have to test RLS in the workspace, the Live Connection will inherit RLS rules from the parent dataset since they're part of the data model.

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

[dwel0120]

@otravers 

This worked! Thank you so much for all of your help!!

[dwel0120]

Yes, that is correct "Get Data > Power BI datasets" . They are two separate files.