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;
- Row-Level Security in Power BI
- Dynamic Row-Level Security
- Row-Level Security for Users and Profiles; A many-to-many pattern
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;
[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 EXCEPT rule;
var vProfileIds= SELECTCOLUMNS( FILTER( 'User Profile', Related(User[Email])=UserPrincipalName() ), 'ID',[Profile]) var vCountryExcept= SELECTCOLUMNS( FILTER( 'SalesTerritoryCounty Except', 'SalesTerritoryCounty Except'[Profile ID] in vProfileIds ), 'SalesTerritoryCountry','SalesTerritoryCounty Except'[SalesTerritoryCountry]) return Not([SalesTerritoryCountry] IN vCountryExcept)
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:
Video
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.