Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' fact table and a 'Product' dimension table, with a one-to-many relationship from 'Product' to 'Sales'. The modeler observes that filtering 'Sales' by a specific 'Salesperson' does not affect the 'Product' table, leading to incorrect calculations when trying to count distinct products sold by that salesperson. Which property needs to be adjusted on the relationship to allow filters to flow from 'Sales' to 'Product'?
- AActive/Inactive status
- BReferential integrity
- CCardinality
- DCross-filter direction
Show answer & explanationAnswer & explanation
Correct answer: D. Cross-filter direction
The cross-filter direction property controls how filters propagate across a relationship. By default, in a one-to-many relationship, filters flow from the 'one' side to the 'many' side. To allow filters to flow from the 'many' side ('Sales') back to the 'one' side ('Product'), the cross-filter direction needs to be changed from 'Single' (default) to 'Both'.
Why the other options are wrong
- A. The active/inactive status determines if a relationship is used by default; while an inactive relationship won't filter, changing it to active doesn't change the direction of an *already active* relationship.
- B. Referential integrity ensures data consistency between related tables but does not control the direction of filter propagation.
- C. Cardinality defines the nature of the relationship (e.g., One-to-Many) and is fundamental, but not the property that directly controls filter flow direction after the relationship is established.
Relationship Cross-filter Direction
The cross-filter direction property in a semantic model relationship defines the direction in which filters propagate between related tables, being either unidirectional ('Single') or bidirectional ('Both').
- Crucial for controlling how filters affect related tables.
- Default for one-to-many is 'Single' (from 'one' to 'many').
- Setting to 'Both' allows filters to flow in both directions.
- Can impact query results if not set correctly for desired analysis.
Memory trick: Relationships: Cardinality, Direction, and Activity!