Bi-Directional Cross-Filtering in Analysis Services Tabular 2016 for Dynamic Row Level Security - Part 2

By:   |   Comments   |   Related: > Analysis Services Development


With the release of SQL Server 2016, Analysis Services Tabular introduces a very important new feature: Bi-direction cross-filtering. This feature allows us to support more complex models much easier without the need of creating extra - often complicated - DAX measures. In part 1 of the tip, we introduced how you can set-up many-to-many relationships using bi-directional cross-filters. Now we'll discuss a slight alternative use cases: dynamic row-level security.


Dynamic Row-level Security

A common scenario is to secure the data according to the privileges of the user viewing the data. For example, users of a specific department can only see sales data for Europe, while users from another department can only view sales data for the United States. When a user views the data, it will be already pre-filtered by the model. If another user takes a look at the same report, he/she will see another set of data, depending on the privileges. In order to set this up we need to add two tables to our model. The first table is a distinct list of the users who have access to the data. The other table is a bridge table containing a list of all of the categories a specific user can view, along with the domain login for the user. Keep in mind the Power BI service uses the email address (the UPN) instead of the traditional domain login. The domain login works fine for local SSAS Tabular instances or when you are working with local users (as I do in this example).

m2m security bridge table

The list of distinct users is simply extracted from the bridge table using a view:

security users view

After importing the bridge table and the view into the model, we now have the following structure:

Let's find out how we can deal with this issue. The entire model has the following structure:

security model

Since those two tables are helper tables to implement row-level security, they are both hidden from client tools. It is now easy to see the set-up for dynamic row-level security is a variation on the many-to-many relationship pattern we discussed in part 1 of the tip. Here we also need to set the relationship to bi-directional. If we don't, row-level security won't take effect since the fact table isn't filtered. Let us configure the security first. In the menu, click on Model and then on Roles... This will bring us to the Role Manager. Here we can create a new role and specify a DAX filter on top of the SecurityUsers table:

role manager

In the members tab, we add the users to the role.

role manager add members

If we would use a front-end tool to analyze the data using UserA, we get the following results:

security fail

We need to change the relationship between the Product Category table and the bridge table to bi-directional and make another additional change as well: we need to configure the relationship to apply filters from row-level security.

security relationship config

When we the relationship is properly configured, we get the results we were looking for (screenshot shows data for UserA):

Accurate results with the correct security

You can read more about row-level security in the blog post Dynamic security made easy with SSAS 2016 and Power BI by Kasper de Jonge.


In this tip, we have shown how the bi-directional cross-filtering feature of Analysis Services Tabular 2016 makes implementing dynamic row-level security easy. In the next part, we'll take a look at another use case for bi-directional cross-filtering: measures on top of dimensions.

Next Steps
  • If you want to check out the Tabular model shown in the tip, you can download it here. Make sure to change the connection strings to the source data.
  • Check out the first part of this tip, where we implemented many-to-many relationships.
  • You can find more Analysis Services tips in this overview.
  • For more SQL Server 2016 tips, you can use this overview.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms