But if you want to understand how it works, it is better to start from the beginning, looking at the calculated columns before. You can skip the following section going directly to “Defining Calculated Measures” if you just want to look at the best solution. However, it is interesting to look at the results that can be obtained defining calculated columns, just to understand how PowerPivot works under the hood. In PowerPivot we define the same three tables (Customers, Accounts and Cross) and then we define these relationships:Īs we will see, the best result is obtained using calculated measures. The many-to-many relationship between Customers and Accounts is expressed by the following Cross table. As we will see, it is necessary to use calculated measures to get the best results.Ĭonsider two tables, Customers and Accounts. Some workaround is possible using DAX, but there are some undesirable side effects if we only use calculated column. Apparently, we can do the same in PowerPivot, but the behavior is not the expected one. In a relational world, a many-to-many relationship is materialized using a bridge table that split this relationship in two separate one-to-many relationships between the two original tables and the bridge table. PowerPivot doesn’t have the capability of really understand a many-to-many (M2M) relationship between two tables. UPDATE: This blog post is still good for learning DAX principles, but a better description of many-to-many patterns is available on The Many-to-Many Revolution whitepaper that is available here: – please download it and use the pattern described there to write faster DAX formulas!

Creating a relationship in power pivot