top of page

Setting up RLS on a Direct Lake semantic model

Updated: Jan 26

Power BI semantic models have the option to set up row level security (RLS). Direct Lake Power BI semantic models in Microsoft Fabric also can have RLS, but the RLS UI is not currently available when creating or editing the model in the Power BI service (in the browser). The RLS UI will be available soon, but for those needing it now, read on.


This tutorial shows you how to set up RLS with Tabular Editor 2 (free) which is one of the many tools utilizing the XMLA endpoint. You can also do it in SSMS (also free) but I haven't included those steps (yet). Let's get started!


First, some pre-requisites :


  1. Turn on your Microsoft Fabric trial or have access to a paid Microsoft Fabric capacity.

  2. Create a workspace and make sure it's in the Trial or Fabric capacity.

  3. Create a Lakehouse and populate delta tables with data. I have a blog post to show you how to do that with generated data!

  4. Create a New semantic model from the Lakehouse or SQL analytics endpoint, or Warehouse. This tutorial is not for the default semantic model, it is for a custom one created from that New semantic model button.

  5. Turn on editing of data (semantic) models for the workspace. Workspace Settings > Power BI > General > Data model settings


6. Turn on the read/write XMLA endpoint for the trail or Fabric capacity. Top -right cog > Admin Portal (you do not need to be the Power BI tenant admin) > Capacity settings > (close any banner that may show) > Trial > Click on the NAME of the capacity (not the cog) > XMLA Endpoint dropdown should be changed to Read/Write. Also see https://learn.microsoft.com/en-us/power-bi/enterprise/directlake-overview#model-write-support-with-xmla-endpoint.


7. Get the workspace XMLA endpoint/workspace connection. Workspace settings > Premium > Workspace connection copy.


Phew! :)


Second, before you start, some words of caution.


Once you have edited the semantic model with XMLA you can edit it in the Power BI service again! But, if you want to try setting up RLS out on an existing semantic model without impacting your model running in production, I suggest you simply duplicate the semantic model first.


To duplicate the Direct Lake semantic model in Tabular Editor 2:

  1. Open Tabular Editor 2.

  2. Connect to the workspace (use the workspace connection you copied) and choose the semantic model you want to duplicate.

  3. Go to Model > Deploy

  4. You should see the same workspace you specified in step 2, otherwise paste it in again. You can use a different Fabric workspace if you want, but this demo assumes you used the same one.

  5. Give it a new name, I just appended XMLA to the end of the existing name.

  6. Click Deploy.

  7. Go to the Power BI service and navigate to the workspace. You should see it there!

8. Finally, refresh the model to make sure the tables are processed and runs in Direct Lake without falling back to DQ.


The default connection from the Direct Lake semantic model to the Lakehouse is single sign-on. This means the person looking at the report accesses the Lakehouse delta table data using their credentials. You can and should change this to be a fixed identity. Fixed identity is when the model owner goes into the semantic model settings and specifies their credentials to be used no matter who is looking at the report. The RLS in the semantic model still knows who is looking at the report and applies the semantic model RLS rules for that user. But the model accesses the Lakehouse table data with the fixed identity credentials to show the visuals in the report.


If you give the user "Viewer" permission to the workspace (not recommended if this is data you want secured) then they can read all the data in the Lakehouse when clicking on the Lakehouse directly. The RLS on the semantic model does not limit their access to the Lakehouse. The data is only limited when viewing reports using that semantic model. This is illustrated in the workspace lineage view by the direction of the arrows.



If you give the user "Viewer" permission when you publish the report as a App (recommended to secure data), then they won't have any access to the underlying Lakehouse. The fixed identity (of someone who can view the Lakehouse data) is required for them to see any data with the semantic model.


This is the same as giving User A permission to your SQL database, then User A creates a semantic model and publishes the model giving Power BI service their credentials to access the data. Then User A shares the reports built from the model with User B (who doesn't have access to the SQL database) to see a only the data User A defined with RLS on the semantic model for User B.


In Direct Lake scenarios the change is now the data source can live in the workspace as the semantic model, so granting workspace permission may be more broad than intended. Just like if User B did have access to the SQL database, then while they may be limited in the reports shared with them by User A, they may not be limited when they go to the SQL database directly.


To change to fixed identity:

  1. Go to the workspace, find the semantic model, and in the context menu choose Settings.

  2. Expand Gateway and cloud connections.

  3. In the drop down you should see "Single Sign-On". Choose New connection.

  4. Enter the name, choose OAuth 2, click Edit credentials (to add your credentials), then Create.

5. That should put you back in the settings page. Important step! Again expand Gateway and cloud connections and change it to the new connection you just created!

6. Click Apply.

7. Finally, go back to the workspace and refresh the semantic model.


Finally, now let's add row level security (RLS)!


  1. Open Tabular Editor 2.

  2. Connect to the workspace (use the workspace connection you copied) and choose the semantic model you want to add RLS to (for this example I use the duplicate one with XMLA at the end, DemoRLSXMLA).

  3. Right-click Roles and choose New Role.

  4. Give the role a helpful name, for my example I am limiting the geography table to Australia so I name it "RLS for Australia".

  5. In the properties pane, expand Row Level Security and enter the DAX formula for the appropriate table. Here I pick 'geo' table and to limit to Australia I use the DAX formula: [Country] = "Australia".

6. Scroll down a bit more and set the Model Permission to "Read".

7. I can set up the Members (who the RLS will apply to) here but I found I wasn't quite sure what to put for the values so I chose the easier path of using the Power BI service to do that part. I save the model (CTRL+S). Then close Tabular Editor 2.

8. Then I go to the Power BI service workspace and in the context menu of the semantic model (again for my demo it's RLSDemoXMLA) I choose Security. In the Security section I can simply add in another user, in my case my Demo user.


9. If you are curious to see what this Members addition does look like in Tabular Editor 2, like I did, you can simply open Tabular Editor 2 and open the model again. Then you can see how it was populated.


10. Now to test this RLS! I created a report in the Power BI service (I could have created it in Power BI Desktop with a live connection and published the report too).


11. I now share my report as in a Power BI app.



This way I can specify the report or reports.



And share it with just Demo user or the whole organization.



This ensures viewer access to the report only, through the semantic model that I set up the RLS on, and not the Lakehouse directly (where they are not limited on what they can see). For them to see any data in the report, the semantic model should be accessing the data in the Lakehouse using fixed identity as noted above, as the semantic model needs credentials of someone who does have access to the data.


Alternatively, I could add my Demo user account to the workspace as a Viewer. As a viewer, Demo will see the report limited per the RLS rules on the semantic model. In this case, only seeing Australia's data. But especially for Direct Lake, this is not recommended. The Lakehouse is also an artifact in the same workspace. This means Demo can go to the Lakehouse and see data for all countries, if they wanted to. The semantic model only impacts downstream artifacts, such as reports. Upstream artifacts are not limited by the semantic model RLS. Other workspace roles, such as Member, Contributor, and Admin, can edit the workspace semantic models, so users with those roles are also not limited by RLS.


I can give the published app link to my Demo account.


12. Now if I log in as Demo and view the same report using the app link, I see only Australia!


If they instead see this error "You don't have permission to view the content of Direct Lake table", then you may not have changed the semantic model connection to a fixed identity! See this section above.


So that is it! I have set up RLS on my Direct Lake semantic model!


Now some additional things to keep in mind.


  • The RLS is only applied to the semantic model and it's downstream reports (check the lineage view). If I added Demo to the workspace as viewer, they can go to the Lakehouse or SQL analytics endpoint and still see all countries data. I would need to set up additional RLS at that layer to limit what they see there. Alternatively, I should publish the report in an app and give Demo viewer permission only to the report as part of that process, so they can't access the Lakehouse directly.

  • If RLS is set up for Demo at the upstream layer of the SQL analytics endpoint, then unfortunately Direct Lake will fallback to DirectQuery. Not only can the report be slower but also keep in mind that the RLS/OLS defined at the SQL Analytics Endpoint will apply too, in addition to the semantic model RLS.

  • The default connection from the Direct Lake semantic model to the Lakehouse is single sign on. This means the person looking at the report will access the Lakehouse tables using their credentials. You can change this to be a fixed identity. See instructions at https://learn.microsoft.com/en-us/power-bi/enterprise/directlake-fixed-identity. Fixed identity is when the model owner goes into the semantic model settings and specifies their credentials to be used no matter who is looking at the report. The RLS in the semantic model will still know who is looking at the report and apply the semantic model RLS rules for that user. But data will be accessed with the fixed identity credentials to load the visuals from the Lakehouse.

  • In this example I filtered the Geo dimension table, that is, a table that has a relationship to another table in your model. If, after doing that, your visuals show a higher total than expected (the total is not filtered from the other table) then check the relationship between the tables. You want to check the Security Filter Behavior and make sure it is set to One Direction or Both Directions, not None. None will ignore the RLS you just set for some relationship types! You can set the Security Filter Behavior in Tabular Editor by clicking the relationship and looking in the properties section. You can also check it with a DAX query via DAX studio, SSMS, or Tabular Editor 3 for Direct Lake with this INFO.Relationships DAX query. And DAX query view for other semantic models created in Power BI Desktop.



523 views0 comments

Recent Posts

See All
bottom of page