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

Posted on by Reza Rad

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;

[Email] = userprincipalname()

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

[ID] IN
SELECTCOLUMNS(
FILTER(
'User Profile',
Related(User[Email])=UserPrincipalName()
),
'ID',[Profile])

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;

var vProfileIds=
SELECTCOLUMNS(
FILTER(
'User Profile',
Related(User[Email])=UserPrincipalName()
),
'ID',[Profile])
var vCountryExclude=
SELECTCOLUMNS(
FILTER(
'SalesTerritoryCountry Exclude',
'SalesTerritoryCountry Exclude'[Profile ID] in vProfileIds
),
'SalesTerritoryCountry','SalesTerritoryCountry Exclude'[SalesTerritoryCountry])
var vCountryInclude=
SELECTCOLUMNS(
FILTER(
'SalesTerritoryCountry Include',
'SalesTerritoryCountry Include'[Profile ID] in vProfileIds
),
'SalesTerritoryCountry','SalesTerritoryCountry Include'[SalesTerritoryCountry])
return
Not(IsBlank(COUNTROWS(FILTER(User,User[Email]=USERPRINCIPALNAME()))))
&&
if(
countrows(vCountryExclude)>0,
Not([SalesTerritoryCountry] IN 
vCountryExclude),
true)
&&
if(
countrows(vCountryInclude)>0,
[SalesTerritoryCountry] IN 
vCountryInclude,
true)

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

    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.

    Leave a Reply