Bi-Directional Cross-Filtering in Analysis Services Tabular 2016 - Part 1

By:   |   Comments (2)   |   Related: > Analysis Services Development


With the release of SQL Server 2016, Analysis Services Tabular introduces a very important new feature: bi-directional cross-filtering. This feature allows us to support more complex models much easier without the need of creating extra - often complicated - DAX measures. In this tip, we'll introduce bi-directional cross-filtering and discuss a couple of use cases where you can use it.


The concept of bi-directional cross-filtering is simple: it allows a relationship to be filtered in both directions. In previous versions, a relationship was always defined as a one-to-many relationship, with the filter direction flowing from the one to the many side.

filter direction in bi-directional cross filtering

In the screenshot above, it means if you filter on the product category, the relevant subcategories will be filtered. However, if you would filter directly on sub categories, the product category table would be unaffected. This behavior made it difficult to implement some scenarios, such as a many-to-many relationship. In most cases, you had to construct special DAX measures using the CALCULATE function.

With bi-directional cross-filtering, you simply enable the relationship to be filtered in both directions. Let's take a look at some examples where we can use this to our advantage. Note that everything described in this tip is applicable to Power BI Desktop as well, as it's basically the same model engine behind the scenes. Currently, Excel 2016 (Power Pivot) doesn't support bi-directional cross-filtering.

Many-to-many Relationships

A many-to-many relationship is characterized by the presence of a bridge table between two other tables. In data warehouses those two tables are typically dimensions, for example a bank account dimensions and a bank account owner dimension (an owner can have multiple bank accounts and a bank account can have multiple owners). Such a bridge table is typically called a factless fact table.

many to many problem with a factless fact table

The AdventureWorks data warehouse sample database contains a many-to-many relationship: an Internet Sales is accompanied with one or more Sales Reasons. A sale can have multiple reasons, but for a reason you can also have multiple sales. In the screenshot you can see the problem: when you filter on reason, the bridge table FactInternetSalesReason is filtered, but the filters do not propagate to the intermediate dimension vSalesOrders. Thus, the fact table is never filtered. The vSalesOrders dimension is a view created in order to link the fact table to the bridge table. It only contains a distinct list of the concatenation of Sales Order header and line numbers. Tabular only supports relationships on single columns, so the sales order header and line numbers are concatenated together using calculated columns in the fact table and bridge table.

sales order header and line numbers are concatenated together using calculated columns in the fact table and bridge table

In Tabular 2012/14 you had no other choice than create extra DAX measures using CALCULATE to solve this issue. Since Analysis Services Multidimensional supports many-to-many relationships out of the box, this use case was a big disadvantage for Tabular models and a reason to keep developing OLAP cubes with SSAS Multidimensional.

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

sample data model for the data warehouse

Unnecessary columns have been removed from the model and columns not needed for the end user are hidden. You can find a link to download the model at the end of the tip. When we analyze the model in Excel, we can see the many-to-many relationship does not work: the count of the sales are repeated for each sales reason; the fact table is not filtered.

the many-to-many relationship does not work: the count of the sales are repeated for each sales reason; the fact table is not filtered

With a simple setting, we can rectify the situation. In the model, we need to set the relationship filter direction to "To Both Tables".

In the model, we need to set the relationship filter direction to "To Both Tables"

When we refresh the Pivot Table, we can see the effect of the setting:

Many to Many succeed

By setting a simple property, we have avoided complex modeling and the creation of extra measures.

Note: it is possible that when you create the model, the SQL Server Data Tools (SSDT) will detect the nature of the relationships and will set relationships automatically to bi-directional.


In this tip, we have shown how the new bi-directional cross-filtering feature of Analysis Services Tabular 2016 has simplified the set-up for many-to-many relationships. In the next part, we'll take a look at other use cases for bi-directional cross-filtering, such as row level security and 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.
  • 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

Wednesday, February 7, 2018 - 8:56:38 AM - Koen Verbeeck Back To Top (75128)

Hi Mithu,

it's hard to say without looking at the actual model.
However, your issue is exactly the reason why people advocate to reduce the use of bi-directional filters.

Maybe you can try it through DAX with CROSSFILTER:

This is the DAX equivalent of bi-directional filters and can be more selective.

Wednesday, February 7, 2018 - 3:43:50 AM - Mithu Back To Top (75125)

Hi Koen ,

I have a Tabular data model in which there are several Facts joined to the customerdimension. I need to set bidirectional Filters between the customer Dim and all the Fact Tables , I Could do this for one fact table but for the others I am getting an ambiguous path error message . Are you aware if multiple bidirectional filters can be set on multiple relations involving one table ? Can you suggest how to tackle this problem ?




get free sql tips
agree to terms