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.
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.
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;
- Row-Level Security in Power BI
- Dynamic Row-Level Security
- Row-Level Security for Users and Profiles; A many-to-many pattern
- Dynamic Row-level security for Not In Rule
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;
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: