Row-Level Security on a DirectQuery to Power BI dataset composite model: My Findings

Row-level security in DirectQuery to Power BI dataset model

With the DirectQuery to Power BI or Azure analysis services dataset, there are some limitations on the way that RLS (Row-level security) works. Considering that I have written a book and many blogs on this subject, It was fair to have a look at how this works. In this blog and video, you will learn about my findings, limitations, and workarounds.

Prerequisite

To understand the concepts of this blog and video, it is important to know what the DirectQuery to Power BI dataset is, and also to know what the Row-level security is and how it can be configured. I recommend these resources:

Row-level security in the source Power BI dataset

One of the first thing I found was that; the RLS configuration of a Power BI dataset, remains in that dataset. It cannot be edited or changed in the chained dataset (A chained dataset is a dataset that uses another dataset as a DirectQuery).

Here I have a Power BI dataset with RLS configuration in the model:

Static RLS defined in a Power BI dataset

The above dataset is published to a workspace in Power BI service, and users configured to have access to the roles (Please note that for simplicity, I built my demos with static row-level security. Same rules applies for dynamic RLS).

users assigned to RLS roles in the Power BI service

Then I created a new Power BI dataset with a DirectQuery to the dataset above, and with another table imported.

Chained dataset: with two tables from a DirectQuery to Power BI dataset and one imported table

In the configuration above, the FactInternetSales is an imported table and the other two tables are DirectQuery to the previous Power BI dataset (Which has the RLS configuration). As you can see, in the RLS configuration, I can only configure the tables that are imported, not the tables that are DirectQuery to another Power BI dataset.

In a chained dataset (DirectQuery to Power BI dataset) you can only configure new RLS rules on the imported tables.

This means that you cannot edit the RLS configuration of the DirectQuery tables. From my point of view, this makes absolute sense. Because the configuration on the source means that users of this dataset should see a partial view of the data. If the underlying dataset can change the RLS in the source dataset, then that would make the whole security configuration useless.

The user’s view

The user’s view of this new dataset would be still filtered. Even though the user would require Build access on the original dataset, still the view access would bring RLS rules down to the user. However, for the imported tables, the story is different.

To understand how it works, let’s have a closer look at the data model:

The RLS configured in the original Power BI dataset

The RLS configuration in the original dataset, will filter the DimSalesTeritorry table. Considering that we have the relationship one-to-many and single-directional from this table to the other two tables, It means that the other two tables should get filtered too. However, the experience is different. This is what the user will see;

The view of the user on a chained Power BI dataset with the RLS configured in the source dataset

FactResellerSales table, which is a table in the source dataset, does get filtered properly, and the RLS filter propagates correctly. So both the total ($53.61M) and the dark blue column in the chart shows correct result.

FactInternetSales table, which is the imported table, acts differently. If the value of that table is used in a chart sliced and diced by a field from DimSalesTerritory, then we see the filtered data correctly. The light blue in the above chart.

However, if the value is used without something from the DimSalesTerritory, then we see the entire tables, data, which is $29.36M. This means that even though, there is a relationship between the DirectQuery table and the imported table, the relationship doesn’t pass the RLS configuration. This is, of course, along with what Microsoft’s documentation explained already.

Workaround

This limitation might be lifted in the future. However, for now, if you want to get the RLS filters to navigate down to other tables in the model even to imported one, you can force the relationship with a measure like this:

I have created a DAX measure using TREATAS function (there are other functions you can use too) as below;

internet sale meausre using treatas = 
CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    TREATAS(
        VALUES(FactInternetSales[SalesTerritoryKey]),
        DimSalesTerritory[SalesTerritoryKey]
        )
    )

The above measure if used in the report, will returns the filtered data even if used in a visual without DimSalesTerritory;

Using measures to pass the filter from RLS defined in DirectQuery tables into imported tables

This workaround however, is just changing the total number, doesn’t get the table filtered. If you want to filter the table, you might need to add some RLS roles for the imported table too. That might require a blog post for itself, this post is mostly about findings than the workaround.

This is not a solution, of course. And this is not saying the RLS doesn’t work in a chained dataset. It is just the way the relationship between a DirectQuery table and an imported table works that make things a bit different. I am hopeful that this limitation is lifted soon by the Power BI team. However, until then, you have a workaround.

Share your thoughts with me and let me know if you face any RLS scenarios that doesn’t work in the chained dataset.

Video

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

6 thoughts on “Row-Level Security on a DirectQuery to Power BI dataset composite model: My Findings

  • Great post Reza. Just to confirm you mean putting RLS on factinternetsales will allow to filter dimsalesterritory and in turn factreseller as well which will serve our purpose.

    • Hi Triparna
      the DimSalesTerritory and FactResellerSales are already getting filtered. but not filtering the FactInternetSales
      adding an RLS filter rule in FactInternetSales will complete the solution.
      Cheers
      Reza

  • Hi,

    The composite model which I am using doesn’t have RLS applied in direct query tables (coming from Power BI dataset). The requirement is that RLS should be applied to the fact table(imported) which will filter all other tables (even tables coming from Power BI dataset), how will this arrangement work?

    • although you can set RLS rules on the fact table. But I do recommend doing it on the dimension tables. when you apply the rule on the dimension tables they will automatically filter the fact table, but not necessarily the other way around (mainly because of the direction of relationships)

      • To put more context- I have regions in my fact table on which RLS is applied. I don’t have region on my dim table (direct query). I also have around 20 other dim tables (imported) which is connected to the fact table with One to Many relationship. My queries-

        My main table is the fact table, I think in your model the main table is the dim table? DimSalesTerritory? So w.r.t your model, I should apply RLS in DimSalesTerritory and it will filter the rest of the other 2 fact tables as well (imported and direct query)?

        • ideally, the Region should be a dim table too. Any descriptive information is better to be stored outside of the fact table. That is a modeling best practice.
          but for the RLS part; yes, you can apply it on the fact table too. It may not filter other tables that have their directions of relationship to this table. but you can use a method like what I described here to fix that (instead of making them both-directional)
          That said, when it comes to DirectQuery to Power BI dataset model, the RLS is not fully supported by switching between tables of different sources. that still has to come later.
          Cheers
          Reza

Leave a Reply