A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Product' table, a 'Sales' fact table, and a 'SalesPerson' table. The 'Product' table has a 'ProductID' and 'Category' column. The 'Sales' table contains 'ProductID', 'SalesPersonID', and 'Amount'. The 'SalesPerson' table has 'SalesPersonID' and 'Region'. Users need to analyze 'Sales' by 'Category' and 'Region'. However, a 'SalesPerson' can sell products from multiple 'Categories', and a 'Category' can be sold by multiple 'SalesPersons'. The modeler initially created a direct one-to-many relationship from 'Product' to 'Sales' and from 'SalesPerson' to 'Sales'. When trying to filter 'Category' by 'Region', no direct path is found. What is the most appropriate way to establish a relationship that allows filtering 'Category' by 'Region' through 'Sales'?
- AUse the CROSSFILTER DAX function in a measure to enable filtering between 'Product' and 'SalesPerson'.
- BIntroduce a bridging table between 'Product' and 'SalesPerson' to handle the many-to-many relationship.
- CEnsure active one-to-many relationships from 'Product' to 'Sales' and 'SalesPerson' to 'Sales', then enable bi-directional filtering on both.
- DCreate a many-to-many relationship directly between 'Product' and 'SalesPerson'.
Show answer & explanationAnswer & explanation
Correct answer: C. Ensure active one-to-many relationships from 'Product' to 'Sales' and 'SalesPerson' to 'Sales', then enable bi-directional filtering on both.
With active one-to-many relationships from 'Product' to 'Sales' and 'SalesPerson' to 'Sales', enabling bi-directional filtering (cross-filter direction 'Both') on both relationships will allow a filter originating from 'Region' (in 'SalesPerson') to flow through 'Sales' to 'Product' (affecting 'Category'), and vice-versa. This creates a virtual many-to-many path through the fact table.
Why the other options are wrong
- A. CROSSFILTER is used within specific measures to temporarily change filter direction or relationship activity, not to establish the primary filtering path for general reporting needs across the entire model.
- B. A bridging table is typically used when there's no common fact table to connect two dimensions directly, which is not the case here as 'Sales' acts as the bridge.
- D. A direct many-to-many relationship between 'Product' and 'SalesPerson' is often problematic and less efficient than using the fact table as a bridge.
Bi-directional Filtering (Many-to-Many through Fact)
Enabling bi-directional filtering on one-to-many relationships connected by a fact table implicitly creates a many-to-many relationship between the two dimension tables, allowing filters to flow between them.
- Filters flow from both sides through the fact table.
- Allows dimensions to filter each other via a common fact.
- Can impact performance; use with understanding of data model.
Memory trick: Dimensions Meet in the Middle, Fact is the Bridge.