Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Hard
A data modeler is designing a semantic model in Microsoft Fabric. The model will contain a 'Customers' table and an 'Addresses' table. A customer can have multiple addresses (e.g., billing, shipping), and an address can be associated with multiple customers (e.g., shared family address). How should the modeler represent the relationship between 'Customers' and 'Addresses' to accurately reflect this business rule?
- AMany-to-many relationship using a bridge table.
- BOne-to-many relationship from 'Customers' to 'Addresses'.
- CNo relationship, use DAX functions like LOOKUPVALUE.
- DOne-to-many relationship from 'Addresses' to 'Customers'.
Show answer & explanationAnswer & explanation
Correct answer: A. Many-to-many relationship using a bridge table.
The scenario describes a many-to-many relationship: a customer can have multiple addresses, and an address can be linked to multiple customers. In semantic models, many-to-many relationships are best implemented using a bridge (or junction) table that connects the two entities with one-to-many relationships.
Why the other options are wrong
- B. This only allows one customer to have many addresses, but not an address to have many customers.
- C. Not defining a relationship would require complex DAX for every interaction and would not allow proper filtering and aggregation.
- D. This only allows one address to have many customers, but not a customer to have many addresses.
Many-to-Many Relationships
A type of relationship between two tables where records in one table can relate to multiple records in the other table, and vice-versa. It is implemented using an intermediary 'bridge' table.
- Requires a bridge table to resolve in semantic models.
- Bridge table connects two dimension tables with one-to-many relationships.
- Enables correct filtering and aggregation across both tables.
Memory trick: When many meet many, build a bridge between them.