Shared dimensions

In this dataset, the Sales Territory, Product, Date, Currency, and Promotion dimension tables are related to both the Internet Sales and Reseller Sales fact tables. As shown in the following screenshot from the Relationships View, these relationships and the three dimension tables specific to either fact table all have single-direction cross-filtering enabled:

Shared dimension tables

Unlike the shared dimensions, the Reseller and Employee dimension tables are exclusively related to the Reseller Sales fact table and the Customer dimension is exclusively related to the Internet Sales fact table. This is a common scenario for larger models in that fact tables will both share dimensions and maintain their own exclusive relationships to certain dimension tables.

In general, it's recommended to avoid bidirectional relationships between shared dimensions and fact tables when there are also dimension tables exclusive to certain fact tables. This is because such relationships generate filter contexts that business users often don't expect or desire and that don't add analytical value. For example, if the relationship between Promotion and Reseller Sales was revised to allow for bidirectional cross-filtering, a report that analyzed internet sales by customers would be impacted by the filter selections of the Reseller and Employee dimension tables even though these two tables are not related to Internet Sales.

In this example, the filter context would flow from the Reseller and/or Employee tables to Reseller Sales but then, via the bidirectional relationship with Promotion, also filter the Promotion table, and finally filter the Internet Sales fact table. In almost all scenarios, the business would expect the Reseller and Employee tables to only filter the Reseller Sales measures. For the rare cases in which this filtering behavior is useful or needed, bidirectional cross-filtering can be enabled for specific measures via the CROSSFILTER() function.

A better use case for bidirectional relationships is between the exclusive dimension tables and their fact tables, such as from Reseller to Reseller Sales or from Customer to Internet Sales. These bidirectional relationships aren't required given the CROSSFILTER() function and other options available in DAX, but they allow simple measures against these dimensions, such as the count of resellers to reflect the filter selections applied to other Reseller Sales dimensions, such as Sales Territory and Product.

Bidirectional cross-filtering is also not allowed for certain relationships due to the ambiguity this would create. In this dataset, Power BI Desktop rejects bidirectional relationships between the Sales Territory, Product, and Date dimension tables with the Internet Sales and Reseller Sales fact tables because this would create more than one filter path to the Sales and Margin Plan fact table. For example, a bidirectional relationship between Sales Territory and Reseller Sales would allow the Product table to either filter the Sales and Margin Plan table via the Product Subcategory bridge table, or filter the Reseller Sales table and then utilize the new bidirectional relationship to filter the Sales Territory table and then its bridge table to the Sales and Margin Plan table. Rather than guess at the correct or intended filter behavior, Power BI will throw an error and advise of the tables associated with the ambiguous condition.

Table of Contents for Shared dimensions

Create new playlist

Sign In

Sign Up

Table of Contents for
Shared dimensions