Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data modeler is designing a semantic model in Microsoft Fabric. The model includes a 'Sales' table and a 'Product' table. There is a one-to-many relationship between 'Product'[ProductID] and 'Sales'[ProductID]. Business users frequently filter sales data by product attributes (e.g., 'Product Category', 'Product Brand'), and also need to see total sales for products that have never been sold. What should be the cross-filter direction for this relationship?
- ASingle (Sales to Product)
- BSingle (Product to Sales)
- CNone
- DBoth
Show answer & explanationAnswer & explanation
Correct answer: B. Single (Product to Sales)
To filter sales by product attributes, the filter needs to flow from 'Product' to 'Sales'. Additionally, to see products that have never been sold (i.e., products with no matching sales), the filter must originate from the 'Product' table, making 'Single (Product to Sales)' the correct choice.
Why the other options are wrong
- A. Single (Sales to Product) would allow filtering products based on sales, which is not the primary requirement and would not show unsold products.
- C. None would prevent any filtering between the tables, making the model unusable for the stated requirements.
- D. Both cross-filter direction can lead to ambiguity and performance issues, and is not strictly necessary here.
Cross-filter Direction (One-to-Many)
Specifies how filters propagate between tables in a relationship. In a one-to-many relationship, the default and often desired direction is from the 'one' side (dimension) to the 'many' side (fact) for filtering.
- Determines how filters applied to one table affect related tables.
- Default is typically single direction from 'one' to 'many'.
- Affects calculation context and query results, especially for showing unmatched rows.
Memory trick: One-to-many, filter flows from one, for products unsold, it's the right run.