Many-to-many in SSAS Tabular

Many-to-many in SSAS Tabular

With the release of SQL Server 2016 CTP 3.0 also comes the ability to test the new functionality of Many-to-Many in SSAS Tabular.

This blogpost will cover the aspects of the many-to-many feature from SQL Server 2016 – including:

  • Prerequisites
  • The old way
  • The new way

This post is based on data from the AdventureWorksDW2012 database.


In order to test the new many-to-many feature from SQL Server 2016 SSAS Tabular you’ll need to download the latest CTP from Microsoft – it can be found here:

Also you’ll need the Visual Studio 2015 and the add-in for Business Intelligence:

Choose the SSDT October 2015 Preview in Visual Studio for download.

After a bit of waiting with the installation, you are ready to test the functionality.

The old way

Before showing the new (and for me right way) to do the many-to-many in SSAS Tabular, let me first show you how it was done prior to SQL Server 2016 CTP 3.0.

Thanks to the two brilliant guys from SqlBI Marco Russo (T,L) and Alberto Ferrari (T,L) we’ve had below approach for quite a while now.

First of all you need to build a bridge table with the column that links the two tables and build a model like below illustrates.

The m2mKey is a concatination of the SalesOrderNumber and SalesOrderLineNumber as the Tabular still does not have the ability to handle two joins at the same time.


Then all measures that need to take the DimSalesReason into account needed to be rewritten with some DAX coding:

Sum of UnitPrice:=CALCULATE(SUM([UnitPrice]);vBridgeSalesReason)

Then the output will look something like this:


The new way

With the CTP 3.0 release and the SSDT addon for Visual Studio 2015 now this get’s as easy as 1,2,3.

First of all, it is now possible to build a datamodel directly without any bridge tables like this:


Note the highlighted area – here you can see the many-to-many relationship. This is modelled when creating the relationship in the model like this:


Remember to select the Filter Direction to << To Both Tables >>.

And that is it!

The result without doing DAX formulas:



Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.

Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

2 thoughts on “Many-to-many in SSAS Tabular

  1. Great!
    Does this work with DirectQuery also?
    If yes, then is there any imporvement on DirectQuery Many-to-many SQL performance using this technique?

    1. H,

      Thank you for your comment and reply. I’m sure that the DirectQuery methology will work here to. Then each partition could point to each individual table containing data. I think that would make an impact on performance – if the relational database delivers data fast enough.

      If you try this approach, can you then reply and let me know how it works out?


Leave a Reply

Your email address will not be published. Required fields are marked *