Dynamic Row-Level Security in Power BI with NOT IN or NOT EQUAL Rule

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Sometimes when you apply the dynamic row-level security, you want to have the criteria as NOT EQUAL and NOT IN. This can be a bit tricky in the dynamic implementation of RLS, because the relationship behavior is normally the opposite. In this article I’ll explain how you can do 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.

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 users table, a profiles table, and the relationship between users and profiles table is many-to-many. We also have a table called SalesTerritoryCountry Except, which has the list of countries that a role should NOT include.

This is the 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.

EXCEPT RULE (NOT equal or Not in)

Implementing the Except rule in a dynamic RLS patterns is a bit different than normal situation. Why you may ask? The reason is the behavior of the relationship in Power BI. Relationship in Power BI means filtering, and by creating a relationship between an RLS table and the tables in the sales pattern, we create a way of filtering that says I just want these values to be INCLUDED.

If you want by selecting a value from a table the EXCLUE happens in the other table, then you should NOT create the relationship.

This is why we should NOT create a relationship between the SalesTerritoryCountry Except table and the DimSalesTerritory table. This is the different part of this pattern.

DAX filter instead of the relationship

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

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.
  • vCountryExcept: this is a table variable with the list of all countries filtered by profiles from the previous variable. These are countries to exclude.
  • return value is all countries that are NOT in the previous variable.

Here is the result;

As you can see in the image above, my user has access to two profiles, and based on that three countries are filtered out in the result set.

Summary

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