上QQ阅读APP看书，第一时间看更新

Many-to-many relationships

Once relationships have been defined in your data model, filtering occurs automatically and this adds a tremendous amount of value to Power BI. However, the analytical value achieved through many-to-many relationships does not happen automatically.

Before you can learn how to handle many-to-many relationships in Power BI, you must first understand the basic behavior of filtering. Let's take a minor detour to explain how filtering works. Filtering will be discussed in more detail in the next chapter. In Figure 8, the total SalesAmount of all transactions is $29,358,677.22. The table visual you see in Figure 8 is simply the sum of the column SalesAmount from the FactInternetSales table:

Figure 8- SalesAmount

To view the total SalesAmount for all transactions broken down by country, all you would need to do is simply add the SalesTerritoryCountry column from the DimSalesTerritory table. This behavior in Power BI is awesome, and this is automatic filtering at work. Take a look at Figure 9:

Figure 9-Viewing total sales amount

Please note that this only works because a valid relationship exists between the FactInternetSales and DimSalesTerritory tables. If a relationship had not been created, or if the relationship created was invalid, then you would get entirely different results and they would be confusing. Let's take a look at what would happen if no relationship had previously existed. In Figure 10, the country has been removed and replaced with the Temperature Range column from the 5 Regions 2008 table:

Figure 10-Replacing Country with Temperature range

Notice how the total sales amount is repeated for each temperature range. This behavior indicates that the 5 Regions 2008 table is unable to filter the FactInternetSales table. This inability to filter can happen for a number of different reasons, and here are a few:

Because a relationship does not exist between the tables
Because an existing relationship is invalid
Because an existing relationship does not allow the filtering to pass through an intermediate table

If you see the repeated value behavior demonstrated in Figure 10, then go back to the relationship view and verify that all relationships have been created and are valid.