Dynamic Row-Level Security in Power BI with Exclude and Include Rules

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

In the previous article, I explained how to implement a dynamic row-level security pattern when you have a value that you want to exclude. However, what if you want to EXCLUDE at some scenarios and INCLUDE in some others? What if you want to have a role saying access to everything but New Zealand, and another role saying just access New Zealand? In this article, I am going to explain that.

Introduction

I have written many articles about row-level security. Implementing a dynamic row-level security is a challenge of data modeling, relationships, and DAX. Based on the scenario, there are different RLS patterns to go through. This article focuses on one of the patterns which is not based on a relationship.

Prerequisite

To understand this article, you need to know what row-level security is. Then you need to understand what dynamic row-level security is, and also to understand how it works in a many-to-many scenario. You also need to understand how to implement a NOT rule in a dynamic row-level security.

I highly recommend to read this three articles before starting this article;

The sample model

The sample model we have here includes two tables, one for sales transactions and another for the sales territory country. Based on the country.

This leads to visualization like below which has the sales by each country;

In this sample model we want to apply a row-level security role as described in below;

Row-Level Security Requirement

We have a user table, a profiles table, and the relationship between users and profiles table is many-to-many. We also have a table called SalesTerritoryCountry Exclude, which has the list of countries that a role should NOT include.

This is the sample data in this table;

We also have a table called SalesTerritoryCountry Include. This table has the list of countries that a role should include. this is a sample data in this table;

and the user, profile, and the User Profile table are connected to this table through the model below;

The many-to-many relationship of the User and Profile table means that we need to apply an RLS rule under the Profile table and the User table. However, there is one more thing about this requirement.

INCLUDE and EXCLUDE in one requirement

If you just have an INCLUDE requirement in a dynamic RLS scenario, then you simply create a relationship between the Include Rule table and the fact table, like the method mentioned here in this article.

If you just have an EXCLUDE requirement in a dynamic RLS scenario, then you should not create a relationship, and you can use DAX filters instead. I mentioned a solution for that in this article.

If you have both INCLUDE and EXCLUDE requirement on the same field of the same table, then creating relationship would cause the same problem as the EXCLUDE. So you need to work with DAX filters to get a proper result. However, writing a DAX expression that can have these two options both in one query can be a bit of challenge.

DAX filter for INCLUDE and EXCLUDE

If we are not using the relationship to apply the filter, then it means we need to use DAX for filtering the values. the criteria would be filter everything BUT the values coming from RLS tables.

The role definition in Power BI would be including three filters as below;

The filter on the User table to get the data for the current user;

Another filter on the Profile table, because this table is not getting filtered by the single-directional relationship of the many-to-many scenario.

If you are still not sure why we need the filter on the profile table, read my article here which explained the reason in detail.

And the last filter on the DimSalesTerritory table to implement the EXCLUDE and INCLUDE rules;

Yes, in dynamic row-level security patterns, DAX is your friend 😉

The DAX expression above is one of the ways to implement the filter. In high level, this is describing what is happening in that last expression:

  • vProfileIds: this is a table variable including all the profile Ids that this user has access to.
  • vCountryExclude: this is a table variable with the list of all countries filtered by profiles from the previous variable. These are countries to exclude.
  • vCountryInclude: this is a table variable with the list of all countries filtered by profiles from the previous variable. These are countries to include.
  • the return value will check the vCountryExclude and vCountryInclude variables and based on those applies the IN or Not In rules. The trick used here is a combination of IF expressions with an alternative value of TRUE just in case there is no rule defined in one table.

Here is the result;

Summary

The trick of combining rules coming from two tables of INCLUDE and EXCLUDE in this pattern was to use a combination of IF statements with TRUE as their alternate values and using AND to combine the IF statements. There will be definitely other ways to implement this pattern.

Dynamic row-level security is the art of modeling, DAX, and relationship. You should know how to connect tables and how to work on the best model with the proper DAX filter to get the desired output. In this article, you learned a common dynamic RLS pattern of Excluding values. If you have any questions, please feel free to let me know in the comments below.

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply