The analysis of actual or historical Sales and Margin Plan versus the Annual Sales Plan is one of the top requirements for this dataset. Given the granularity of the annual Sales and Margin Plan (Calendar Month, Product Subcategory, Sales Territory Region), it's necessary to create bridge tables reflecting the unique values of these columns.

The three bridge tables, which can be hidden from the user interface, enable relationships between the Date, Product, and Sales Territory dimension tables with the Sales and Margin Plan fact table.

In the following example, the Sales Territory dimension table query is referenced as a source and the unique values of the Sales Territory Region column are retrieved via the Table.Distinct() function:

Sales Territory Bridge Table query for actual versus plan analysis

In the data model, the bridge tables will have one-to-many relationships with both the Annual Sales Plan fact table and their associated dimension tables. The relationship between the bridge tables and the dimensions will be set to allow bidirectional cross-filtering such that a filter selection for a Product Category (for example, Bikes) will impact both the historical sales fact tables as well as the Sales and Margin Plan. Greater details of this model will be discussed in Chapter 9, Designing Import and DirectQuery Data Models. The Enable load and Include in Report Refresh properties for each bridge table query (accessible via the right-click menu) should be set to true.

All three bridge table M queries result in simple SQL statements, such as the following, via Query Folding:

"Select distinct [Sales Territory Region] from BI.vDim_SalesTerritory"

Therefore, all three bridge table queries can be used in a DirectQuery dataset. Additionally, these bridge queries could be stored as new SQL views in the source database to eliminate the dependency on M functions.