Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 119 of 212

107 C H A P T E R 6 | More analytics Because the structure of the two fact tables in this example does not allow you to define a relationship between them, you must add a calculated column called Sales Key (or another unique name that you prefer) to each of them to uniquely identify the combination of sales order and line number. To do this, you use a DAX expression similar to this: =[SalesOrderNumber]&"- "&[SalesOrderLineNumber] in each fact table. You can then create a relationship between the two tables using this common column and set the direction to To Both Tables, as shown in Figure 6-12. Figure 6-12: Defining a many-to-many relationship. Then, when you create a PivotTable to review sales counts by sales reason, the many-to-many relationship is correctly evaluated, as shown in Figure 6-13, even though there is no direct relationship between the Sales Reason table and Internet Sales. The grand total continues to correctly reflect the count of sales, which is less than the sum of the individual rows in the PivotTable. This is expected behavior for a many-to-many relationship because of the inclusion of the same sale with multiple sales reasons. Figure 6-13: Viewing the effect of a many-to-many relationship in a PivotTable. Important Although you might be tempted to configure bidirectional filtering on all relationships to address all possible situations, it is possible for this configuration to overfilter results unexpectedly. Therefore, you should test the behavior of each filter direction change to ensure that you get the results you want.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016