Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data engineer is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' table and a 'Customers' table. The 'Sales' table contains 'CustomerID' and 'OrderDate', and the 'Customers' table contains 'CustomerID' and 'CustomerSegment'. Historically, the model has a one-to-many relationship from 'Customers' to 'Sales' based on 'CustomerID', with a single cross-filter direction. A new requirement states that filters applied to 'Sales' (e.g., filtering by 'OrderDate') must also affect 'CustomerSegment' in reports. Which action should the data engineer take to enable this new filtering behavior?
- ACreate a new many-to-many relationship between 'Sales' and 'Customers'.
- BChange the cross-filter direction of the relationship to 'Both'.
- CImplement a DAX measure using the TREATAS function.
- DBreak the existing relationship and create a new one with a different cardinality.
Show answer & explanationAnswer & explanation
Correct answer: B. Change the cross-filter direction of the relationship to 'Both'.
Changing the cross-filter direction to 'Both' for the existing one-to-many relationship will allow filters to propagate from the 'many' side ('Sales') back to the 'one' side ('Customers'). This enables filters on 'Sales' (like 'OrderDate') to affect columns in 'Customers' (like 'CustomerSegment').
Why the other options are wrong
- A. A many-to-many relationship is typically used when neither table has unique values for the join column, which is not the case here (CustomerID is unique in Customers).
- C. TREATAS is used for applying filters from a disconnected table or for advanced filtering scenarios, not for basic cross-filter direction changes in an existing relationship.
- D. Breaking and recreating the relationship with a different cardinality (e.g., many-to-many) is not necessary and would be incorrect given the data model structure (one customer to many sales).
Cross-filter Direction (Both)
The 'Both' cross-filter direction in a semantic model relationship allows filters to propagate from both sides of the relationship, enabling bi-directional filtering.
- Filters flow from 'one' to 'many' and 'many' to 'one'.
- Useful for enabling filtering from fact tables to dimension tables.
- Can impact performance and ambiguity; use judiciously.
Memory trick: One-Way is Simple, Both-Ways is Flexible.