How to implement Many-to-Many Relationships in Tabular model Analysis Service in SQL Server

First, let’s look at how to implement many-to-many relationship in Multi-dimensional cube. The Adventure Works DW provide great example to understand the concept. If you look at the below diagram, there is no relationship between Sales Reason and Internet Sales here. In this case, the bridge table FactInternetSalesReason, bridges the sales reason and from theDiSalesReason dimension to the FactInternetSales fact table by these 2 columns SalesOrderNumber and SalesOrderLineNumber.

The FactInternetSalesReason table can have multiple entries for the same order number and line number.

You see there are no relationship between Sales Reason and Internet Sales in the cube.

Many-to-many relationships are not automatically built through the wizards in the multidimensional cube or tabular model. The relationship needs to configure as per below.

Once implement the appropriate relationship, now shows correct values.

In the Tabular model, Bi-directional cross filters feature is used for many-to-many relationship. New in SQL Server 2016 is a built-in approach for enabling bi-directional cross filters in tabular models, eliminating the need for hand-crafted DAX workarounds for propagating filter context across table relationships.

As I mentioned earlier, there is no relationship between these two fact tables when you import the tables into tabular model. Let’s created a calculated column in both the tables, they can be used to join the 2 fact tables. In this case [SalesOrderNumber] & "-" & [SalesOrderLineNumber] logic used to created the calculated column named called CombinedKey.

Once joined the tables using the CombinedKey and then select filter direction to “To Both Tables”.

Now you can see the correct results while analyzing in Excel.

Cheers!
Uma