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

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 Configuration in Power BI Desktop
• DirectQuery for Power BI datasets and Analysis Services. The composite model with Analysis Services. What is it and why it is a big deal?
• DirectQuery for Power BI dataset: How does it work?
• Dynamic Row Level Security with Power BI Made Simple

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:

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).

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

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.

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 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;

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;

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

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.