Cross-filtering direction

Now that you understand the basics of automatic filtering in Power BI, let's take a look at an example of a many-to-many relationship. DimProduct and DimCustomer have a many-to-many relationship. A product can be sold to many customers. For example, bread can be sold to Jessica, Kim, and Tyrone. A customer can purchase many products. Kim could purchase bread, milk, and cheese. 

A bridge table can be used to store the relationship between two tables that have a many-to-many relationship, just like tools you have worked with in the past. 

The relationship between DimProduct and DimCustomer is stored in the FactInternetSales table. The FactInternetSales table is a large many-to-many bridge table:

Figure 11 shows the relationship between these two tables; see the following explanation for the numbered points:

1. The relationship between DimCustomer and FactInternetSales
2. The relationship between DimProduct and FactInternetSales
3. The cross filter direction is set to single

The following report displays the total sales, total transactions, and customer count for each product:

Let's take a closer look at Figure 12, and note the numbered points:

1. Product Name from the DimProduct table
2. Total Sales is the SUM of the Sales Amount column from the FactInternetSales table
3. Total Transactions is the number of corresponding transactions from the FactInternetSales table
4. Customer Count is the COUNT of the CustomerKey column from the DimCustomer table

Total Sales and Total Transactions are returning the correct results for each product. Customer Count is returning the same value for all products (18,484). This is due to the way that filtering works. The calculations for Total Sales and Total Transactions are derived from columns or rows that come from the FactInternetSales table. The Product table has a one-to-many relationship with Internet Sales, and therefore filtering occurs automatically. This explains why those two calculations are being filtered properly, but it does not explain why the count of customers is returning the same repeated value for all products, not entirely anyway.

Let's take another look at the relationship between DimProduct and DimCustomer. You will notice in the following image that the relationship between these two tables flows through the FactInternetSales table. This is because they have a many-to-many relationship. In this scenario, the table FactInternetSales is acting as a large many-to-many bridge table. DimProduct filters FactInternet Sales. DimCustomer also filters FactInternetSales, and FactInternetSales is currently unable to filter the customer table:

The repeated value for customer count occurs because FactInternetSales is unable to filter the DimCustomer table. DimProduct filters FactInternetSales, and a list of transactions are returned for each product. Unfortunately, the filtering does not pass from FactInternetSales to DimCustomer. This is because FactInternetSales is on the many side of the relationship with DimCustomer. Therefore, when our calculation performs a count on the customer key, the table is not filtered and the calculation sees every customer key in the DimCustomer table (18,484).

Do you remember the cross-filter direction property that was briefly covered earlier in this chapter? That little property is there to provide many-to-many support. By simply enabling cross-filtering in both directions, the FactInternetSales table will be able to filter the customer table and the customer count will work.