Bidirectional Relationships And Many-To-Many In The Power BI Designer

There’s a lot of cool stuff in the new Power BI Designer desktop app, but for me the most important new bit of functionality is one that’s not immediately obvious: relationships between tables in the data model have had a significant upgrade. Let me illustrate…

Bidirectional Relationships

First up: relationships can now filter in two directions. Consider the following two tables in an Excel workbook, a dimension table called Fruit and a fact table called Sales:


When you first load these tables into the Power BI Designer no relationships are created between the tables. To create relationships you need to click on the Manage button on the Home tab so that the Manage Relationships dialog appears. You can then click the Autodetect button and the relationship between the two FruitID columns is created.


However, click on the Edit button and you’ll see something interesting. In the Edit Relationship dialog, under Advanced options, you’ll see that the Cross filter direction is set to Both (the other option is Single).


This means that, not only can you create a report like this with FruitName field on rows axis of a table in a Power View report along with a measure showing the sum of values from the Sales field:


But you can also now take the Date field from the Sales table and put it on rows in the report along with a measure showing the distinct count of values from the Fruit Name field from the Fruit table:


The relationship between the two tables is working in both directions, from the dimension table to the fact table and from the fact table to the dimension table, which is a big change from Power Pivot in Excel where a relationship can only work in one direction (from the dimension table to the fact table). You can still get the original Power Pivot relationship behaviour by setting the Cross filter direction property to Single.


If you thought that was impressive, there’s another implication of this change: many-to-many relationships now work automatically. No nasty DAX is necessary – which is lucky because, at the time of writing, there’s nowhere to use DAX in the Power BI Designer. Here’s the same data as above but with two more tables, so that there is now a classic many-to-many model with a dimension table called Group and a factless fact table called GroupToFruit associating each fruit with one or more groups and each group with one or more fruit.


Here are the relationships in the model, all of which were created using the Autodetect button and all of which have their Cross filter direction set to Both:


And here’s what you see in a report when you put GroupName on rows with a measure showing the sum of Sales:


The sales value for Berries is 35, the sum of the sales for Raspberries and Strawberries; the sales value for Red Fruit is the same because that group contains the same fruit; but the grand total is not the sum of the groups but the total sales for all fruit.


Anyone that has tried to build a reasonably complex model in Power Pivot or SSAS Tabular will understand how big a change this is. Up to now if you wanted to use many-to-many relationships you needed to add extra DAX code to each measure you created, and that added an unwelcome layer of complexity; now it just works. I haven’t thought it through properly yet but I bet that many other modelling scenarios can now be solved with this new functionality too. Time to do some thinking…

33 responses

  1. Great news, I’ve done the M2M, and always a pain! Btw, I assume you can’t still use Power BI website, but I forgot the Power BI Designer is just an app.

  2. Pingback: Dew Drop – January 2, 2015 (#1925) | Morning Dew

  3. Pingback: Power BI Semantic Model Supports Many-to-Many Relationships | Paul Turley's SQL Server BI Blog

  4. Pingback: Power BI Semantic Model Supports Many-to-Many Relationships | Paul Turley's SQL Server BI Blog

  5. Pingback: Power BI Semantic Model Supports Many-to-Many Relationships | Paul Turley's SQL Server BI Blog

  6. Pingback: Power BI, Bilan et Perspectives | Business Geek

  7. Very cool. Reduces the need to do DAX ‘acrobatics’ when dealing with certain models. It also reduces the need for a more ‘purist’ dimensional model, allowing less definition of fact versus dimension tables; This may open up adoption due to reduced complexity, but also reduce reliance on traditional BI schemas (and possibly increasing the perceived gap between SSBI and Corporate BI)…

    One thing I find interesting is why not tag all relationships as bi-directional…… what would be the advantage of keeping the option of ‘single-type’ relationships?

    • I can think of lots of scenarios where you would want to have old-style single-direction relationships. When you have a model with two fact tables, I would say that in most cases you wouldn’t want this kind of many-to-many filtering to happen – it would lead to data being hidden that you wouldn’t want hidden.

      • Chris, so by enabling bi-directional relationships, both tables in the relationship are automatically cross-filtered? In that case, I can see the scenario you describe where we may not want to do this in every case. Somehow, I was hoping bi-directional cross-filtering will only happen depending on which ‘side’ was actually used as the dimension.

  8. Pingback: Power BI And Excel 2016 BI News | Chris Webb's BI Blog

  9. Pingback: Power BI And Excel 2016 BI News - SQL Server - SQL Server - Toad World

  10. Chris,
    Do you know if this bi-directional relationship capability is planned for migration into Excel PowerPivot?
    Is it a goal to keep the two product’s development paths in-synch?

    Chris Gilbert

  11. Pingback: One-To-One Relationships In Power BI | Chris Webb's BI Blog

  12. Pingback: Power BI Desktop: A Guide for Excel Users | Devin Knight

  13. Pingback: Why Is My Relationship Inactive in Power BI Desktop? - SQL Server - SQL Server - Toad World

  14. Pingback: Webinar Recording: Is OLAP Dead? | Chris Webb's BI Blog

  15. Interesting read.

    I have a fact table that contains 1 ID that is not in my main Table, how would I exclude this item in Power Query? (The items was removed from the Main table).

  16. Pingback: Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering And M – Chris Webb's BI Blog

  17. Hi Chris, I have similar issue with many to many relationship. And because of duplicates i cant create relationships between tables. And i couldn’t make data unique. I draw a view of my tables. And url is below. I am sorry for not sharing table names and column names. It is client project. But i hope attached screenshot can give idea. Can you please look at that image and help me find a way for calculating needed measures? Thanks a lot.

    • It’s hard to comment without knowing a lot more about your project, but you will have to remove duplicates somewhere to build the relationships – there is no other option. It sounds like you need to think of a different way to model your data.

Leave a Reply to Aslan Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: