{"id":3377,"date":"2021-06-02T15:11:16","date_gmt":"2021-06-02T22:11:16","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3377"},"modified":"2021-06-02T15:11:18","modified_gmt":"2021-06-02T22:11:18","slug":"how-to-apply-many-many-relationships-in-power-bi-desktop","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3377","title":{"rendered":"How to Apply Many-Many Relationships in Power BI Desktop"},"content":{"rendered":"\n<p>With&nbsp;<em>relationships with a many-many cardinality<\/em>&nbsp;in Power BI Desktop, you can join tables that use a cardinality of&nbsp;<em>many-to-many<\/em>. You can more easily and intuitively create data models that contain two or more data sources.&nbsp;<em>Relationships with a many-many cardinality<\/em>&nbsp;are part of the larger&nbsp;<em>composite models<\/em>&nbsp;capabilities in Power BI Desktop.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_01.png\" alt=\"A many-to-many relationship in the &quot;Edit relationship&quot; pane, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>A&nbsp;<em>relationship with a many-many cardinality<\/em>&nbsp;in Power BI Desktop is composed of one of three related features:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Composite models<\/strong>: A&nbsp;<em>composite model<\/em>&nbsp;allows a report to have two or more data connections, including DirectQuery connections or Import, in any combo. For more information, see&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/desktop-composite-models\">Use composite models in Power BI Desktop<\/a>.<\/li><li><strong>Relationships with a many-many cardinality<\/strong>: With composite models, you can establish&nbsp;<em>relationships with a many-many cardinality<\/em>&nbsp;between tables. This approach removes requirements for unique values in tables. It also removes previous workarounds, such as introducing new tables only to establish relationships. The feature is described further in this article.<\/li><li><strong>Storage mode<\/strong>: You can now specify which visuals require a query to back-end data sources. Visuals that don&#8217;t require a query are imported even if they&#8217;re based on DirectQuery. This feature helps improve performance and reduce back-end load. Previously, even simple visuals, such as slicers, began queries that were sent to back-end sources. For more information, see&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/desktop-storage-mode\">Storage mode in Power BI Desktop<\/a>.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"what-a-relationship-with-a-many-many-cardinality-solves\">What a relationship with a many-many cardinality solves<\/h2>\n\n\n\n<p>Before&nbsp;<em>relationships with a many-many cardinality<\/em>&nbsp;became available, the relationship between two tables was defined in Power BI. At least one of the table columns involved in the relationship had to contain unique values. Often, though, no columns contained unique values.<\/p>\n\n\n\n<p>For example, two tables might have had a column labeled Country. The values of Country weren&#8217;t unique in either table, though. To join such tables, you had to create a workaround. One workaround might be to introduce extra tables with the needed unique values. With&nbsp;<em>relationships with a many-many cardinality<\/em>, you can join such tables directly, if you use a relationship with a cardinality of&nbsp;<em>many-to-many<\/em>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"use-relationships-with-a-many-many-cardinality\">Use relationships with a many-many cardinality<\/h2>\n\n\n\n<p>When you define a relationship between two tables in Power BI, you must define the cardinality of the relationship. For example, the relationship between ProductSales and Product\u2014using columns ProductSales[ProductCode] and Product[ProductCode]\u2014would be defined as&nbsp;<em>Many-1<\/em>. We define the relationship in this way, because each product has many sales, and the column in the Product table (ProductCode) is unique. When you define a relationship cardinality as&nbsp;<em>Many-1<\/em>,&nbsp;<em>1-Many<\/em>, or&nbsp;<em>1-1<\/em>, Power BI validates it, so the cardinality that you select matches the actual data.<\/p>\n\n\n\n<p>For example, take a look at the simple model in this image:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_02.png\" alt=\"ProductSales and Product table, Relationship view, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>Now, imagine that the&nbsp;<strong>Product<\/strong>&nbsp;table displays just two rows, as shown:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_03.png\" alt=\"Product table visual with two rows, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>Also imagine that the Sales table has just four rows, including a row for a product C. Because of a referential integrity error, the product C row doesn&#8217;t exist in the&nbsp;<strong>Product<\/strong>&nbsp;table.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_04.png\" alt=\"Sales table visual with four rows, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>The&nbsp;<strong>ProductName<\/strong>&nbsp;and&nbsp;<strong>Price<\/strong>&nbsp;(from the&nbsp;<strong>Product<\/strong>&nbsp;table), along with the total&nbsp;<strong>Qty<\/strong>&nbsp;for each product (from the ProductSales table), would be displayed as shown:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_05.png\" alt=\"Visual displaying the product name, price, and quantity, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>As you can see in the preceding image, a blank&nbsp;<strong>ProductName<\/strong>&nbsp;row is associated with sales for product C. This blank row accounts for the following:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Any rows in the&nbsp;<strong>ProductSales<\/strong>&nbsp;table for which no corresponding row exists in the&nbsp;<strong>Product<\/strong>&nbsp;table. There&#8217;s a referential integrity issue, as we see for product C in this example.<\/li><li>Any rows in the&nbsp;<strong>ProductSales<\/strong>&nbsp;table for which the foreign key column is null.<\/li><\/ul>\n\n\n\n<p>For these reasons, the blank row in both cases accounts for sales where the&nbsp;<strong>ProductName<\/strong>&nbsp;and&nbsp;<strong>Price<\/strong>&nbsp;are unknown.<\/p>\n\n\n\n<p>Sometimes the tables are joined by two columns, yet neither column is unique. For example, consider these two tables:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The&nbsp;<strong>Sales<\/strong>&nbsp;table displays sales data by&nbsp;<strong>State<\/strong>, and each row contains the sales amount for the type of sale in that state. The states include CA, WA, and TX.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_06.png\" alt=\"Sales table displaying sales by state, Power BI Desktop\"><\/li><li>The&nbsp;<strong>CityData<\/strong>&nbsp;table displays data on cities, including the population and state (such as CA, WA, and New York).<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_07.png\" alt=\"Sales table displaying city, state, and population, Power BI Desktop\"><\/li><\/ul>\n\n\n\n<p>A column for&nbsp;<strong>State<\/strong>&nbsp;is now in both tables. It&#8217;s reasonable to want to report on both total sales by state and total population of each state. However, a problem exists: the&nbsp;<strong>State<\/strong>&nbsp;column isn&#8217;t unique in either table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"the-previous-workaround\">The previous workaround<\/h2>\n\n\n\n<p>Before the July 2018 release of Power BI Desktop, you couldn&#8217;t create a direct relationship between these tables. A common workaround was to:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Create a third table that contains only the unique State IDs. The table could be any or all of:<ul><li>A calculated table (defined by using Data Analysis Expressions [DAX]).<\/li><li>A table based on a query that&#8217;s defined in Query Editor, which could display the unique IDs drawn from one of the tables.<\/li><li>The combined full set.<\/li><\/ul><\/li><li>Then relate the two original tables to that new table by using common&nbsp;<em>Many-1<\/em>&nbsp;relationships.<\/li><\/ul>\n\n\n\n<p>You could leave the workaround table visible. Or you may hide the workaround table, so it doesn&#8217;t appear in the&nbsp;<strong>Fields<\/strong>&nbsp;list. If you hide the table, the&nbsp;<em>Many-1<\/em>&nbsp;relationships would commonly be set to filter in both directions, and you could use the State field from either table. The later cross filtering would propagate to the other table. That approach is shown in the following image:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_08.png\" alt=\"Hidden State table, Relationship view, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>A visual that displays&nbsp;<strong>State<\/strong>&nbsp;(from the&nbsp;<strong>CityData<\/strong>&nbsp;table), along with total&nbsp;<strong>Population<\/strong>&nbsp;and total&nbsp;<strong>Sales<\/strong>, would then appear as follows:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_09.png\" alt=\"Screenshot shows a table with State, Population, and Sales data.\"\/><\/figure>\n\n\n\n<p>&nbsp;Note<\/p>\n\n\n\n<p>Because the state from the&nbsp;<strong>CityData<\/strong>&nbsp;table is used in this workaround, only the states in that table are listed, so TX is excluded. Also, unlike&nbsp;<em>Many-1<\/em>&nbsp;relationships, while the total row includes all&nbsp;<strong>Sales<\/strong>&nbsp;(including those of TX), the details don&#8217;t include a blank row covering such mismatched rows. Similarly, no blank row would cover&nbsp;<strong>Sales<\/strong>&nbsp;for which there&#8217;s a null value for the&nbsp;<strong>State<\/strong>.<\/p>\n\n\n\n<p>Suppose you also add City to that visual. Although the population per City is known, the&nbsp;<strong>Sales<\/strong>&nbsp;shown for City simply repeats the&nbsp;<strong>Sales<\/strong>&nbsp;for the corresponding&nbsp;<strong>State<\/strong>. This scenario normally occurs when the column grouping is unrelated to some aggregate measure, as shown here:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_10.png\" alt=\"State and city population and sales, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>Let&#8217;s say you define the new Sales table as the combination of all States here, and we make it visible in the&nbsp;<strong>Fields<\/strong>&nbsp;list. The same visual would display&nbsp;<strong>State<\/strong>&nbsp;(on the new table), the total&nbsp;<strong>Population<\/strong>, and total&nbsp;<strong>Sales<\/strong>:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_11.png\" alt=\"State, population, and sales visual, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>As you can see, TX\u2014with&nbsp;<strong>Sales<\/strong>&nbsp;data but unknown&nbsp;<em>Population<\/em>&nbsp;data\u2014and New York\u2014with known&nbsp;<strong>Population<\/strong>&nbsp;data but no&nbsp;<strong>Sales<\/strong>&nbsp;data\u2014would be included. This workaround isn&#8217;t optimal, and it has many issues. For relationships with a many-many cardinality, the resulting issues are addressed, as described in the next section.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"use-a-relationship-with-a-many-many-cardinality-instead-of-the-workaround\">Use a relationship with a many-many cardinality instead of the workaround<\/h2>\n\n\n\n<p>You can directly relate tables, such as the ones we described earlier, without having to resort to similar workarounds. It&#8217;s now possible to set the relationship cardinality to&nbsp;<em>many-to-many<\/em>. This setting indicates that neither table contains unique values. For such relationships, you may still control which table filters the other table. Or you can apply bidirectional filtering, where each table filters the other.<\/p>\n\n\n\n<p>In Power BI Desktop, the cardinality defaults to&nbsp;<em>many-to-many<\/em>&nbsp;when it determines neither table contains unique values for the relationship columns. In such cases, a warning message confirms you want to set a relationship, and the change isn&#8217;t the unintended effect of a data issue.<\/p>\n\n\n\n<p>For example, when you create a relationship directly between CityData and Sales\u2014where filters should flow from CityData to Sales\u2014Power BI Desktop displays the&nbsp;<strong>Edit relationship<\/strong>&nbsp;dialog box:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_01.png\" alt=\"Edit relationship dialog box, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>The resulting&nbsp;<strong>Relationship<\/strong>&nbsp;view would then display the direct, many-to-many relationship between the two tables. The tables&#8217; appearance in the&nbsp;<strong>Fields<\/strong>&nbsp;list, and their later behavior when the visuals are created, are similar to when we applied the workaround. In the workaround, the extra table that displays the distinct State data isn&#8217;t made visible. As described earlier, a visual that shows&nbsp;<strong>State<\/strong>,&nbsp;<strong>Population<\/strong>, and&nbsp;<strong>Sales<\/strong>&nbsp;data would be displayed:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_12.png\" alt=\"State, Population, and Sales tables, Power BI Desktop\"\/><\/figure>\n\n\n\n<p>The major differences between&nbsp;<em>relationships with a many-many cardinality<\/em>&nbsp;and the more typical&nbsp;<em>Many-1<\/em>&nbsp;relationships are as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The values shown don&#8217;t include a blank row that accounts for mismatched rows in the other table. Also, the values don&#8217;t account for rows where the column used in the relationship in the other table is null.<\/li><li>You can&#8217;t use the&nbsp;<code>RELATED()<\/code>&nbsp;function, because more than one row could be related.<\/li><li>Using the&nbsp;<code>ALL()<\/code>&nbsp;function on a table doesn&#8217;t remove filters that are applied to other, related tables by a many-to-many relationship. In the preceding example, a measure that&#8217;s defined as shown here wouldn&#8217;t remove filters on columns in the related CityData table:<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_13.png\" alt=\"Script example\">A visual showing&nbsp;<strong>State<\/strong>,&nbsp;<strong>Sales<\/strong>, and&nbsp;<strong>Sales total<\/strong>&nbsp;data would result in this graphic:<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/media\/desktop-many-to-many-relationships\/many-to-many-relationships_14.png\" alt=\"Table visual\"><\/li><\/ul>\n\n\n\n<p>With the preceding differences in mind, make sure the calculations that use&nbsp;<code>ALL(&lt;Table&gt;)<\/code>, such as&nbsp;<em>% of grand total<\/em>, are returning the intended results.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"limitations-and-considerations\">Limitations and considerations<\/h2>\n\n\n\n<p>There are a few limitations for this release of&nbsp;<em>relationships with a many-many cardinality<\/em>&nbsp;and composite models.<\/p>\n\n\n\n<p>The following Live Connect (multidimensional) sources can&#8217;t be used with composite models:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>SAP HANA<\/li><li>SAP Business Warehouse<\/li><li>SQL Server Analysis Services<\/li><li>Power BI datasets<\/li><li>Azure Analysis Services<\/li><\/ul>\n\n\n\n<p>When you connect to these multidimensional sources by using DirectQuery, you can&#8217;t connect to another DirectQuery source or combine it with imported data.<\/p>\n\n\n\n<p>The existing limitations of using DirectQuery still apply when you use\u00a0<em>relationships with a many-many cardinality<\/em>. Many limitations are now per table, depending upon the storage mode of the table. For example, a calculated column on an imported table can refer to other tables, but a calculated column on a DirectQuery table can still refer only to columns on the same table. Other limitations apply to the whole model if any tables within the model are DirectQuery. For example, the QuickInsights and Q&amp;A features are unavailable on a model if any table within it has a storage mode of DirectQuery.<\/p>\n\n\n\n<p>Ref: https:\/\/docs.microsoft.com\/en-us\/power-bi\/transform-model\/desktop-many-to-many-relationships<\/p>\n","protected":false},"excerpt":{"rendered":"<p>With&nbsp;relationships with a many-many cardinality&nbsp;in Power BI Desktop, you can join tables that use a cardinality of&nbsp;many-to-many. You can more easily and intuitively create data models that contain two or more data sources.&nbsp;Relationships with a many-many cardinality&nbsp;are part of the larger&nbsp;composite models&nbsp;capabilities in Power BI Desktop. A&nbsp;relationship with a many-many cardinality&nbsp;in Power BI Desktop is <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3377\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[754],"class_list":["post-3377","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-many-many-relationships"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3377","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3377"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3377\/revisions"}],"predecessor-version":[{"id":3378,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3377\/revisions\/3378"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3377"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3377"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3377"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}