Designing relationships in semantic models
1. Designing relationships in semantic models
In the last lesson, you traced where a model's data comes from. But models rarely hold one table, and reports only work when tables connect correctly. Now you'll build those connections.2. Two table types linked by a shared key
Most models combine two kinds of tables. A fact table records repeated events: each Sales row is one product sold on an order, so a ProductID can appear many times. A dimension table adds context: Products has one row per product, with a unique ProductID, name, and category. ProductID is their shared key, a column holding the same identifier in both tables.3. Relationships connect shared keys
A shared column alone doesn't connect two tables; the model needs a relationship that names the matching columns. To add one, select Manage relationships on the ribbon, then create a new relationship and choose the table-and-column pair. The source creates the tables; the model defines how they relate.4. Cardinality describes how keys match
Each relationship has a cardinality: how many rows on each side can share a key value. Products to Sales is one-to-many, since each ProductID appears once in Products but many times in Sales. One-to-one fits keys unique on both sides, such as Employee and EmployeeProfile, and keys repeating on both sides make many-to-many. In the New relationship dialog, Fabric suggests a cardinality from the values in the chosen columns. Check that the suggestion matches the business rule. For example, Products should list each product once, so if the dialog suggests many-to-many, look for duplicate ProductIDs before you accept it.5. A bridge resolves a true many-to-many association
Some many-to-many patterns are real. Customers receive many promotions, and each promotion reaches many customers. A direct relationship would repeat keys on both sides, making filters hard to predict. Instead, a CustomerPromotion bridge stores each valid CustomerID and PromotionID pair, and Customers and Promotions each connect to it one-to-many, giving two clear paths.6. Choose one default filter path
Sometimes two tables connect in more than one way. Sales has an OrderDate and a DeliveryDate, and both could link to Date. If both carried filters, a selected month would reach Sales two ways, and the model couldn't tell orders placed from orders delivered. So only one relationship can be active, carrying filters automatically. The other stays inactive until a calculation switches it on, for example with the USERELATIONSHIP function. Keep OrderDate active for common analysis and use DeliveryDate for delivery timing.7. Filtering direction
Each relationship has a direction, set as the Cross-filter direction in the dialog. With Single, the default, filters flow one way: from the dimension to the fact. Select one product, and Products filters Sales down to that product's rows. In a star schema, with dimensions around one central fact table, that one-way flow keeps results predictable. If you change the setting to Both, filters also flow back along the dashed arrow, so Sales can filter Products too. That helps some specific designs, but across several tables, two-way relationships can form loops, where a filter reaches the same table by two paths, making results unpredictable.8. Calendar tables and date hierarchies
Dates usually get their own dimension: a calendar table, often named Calendar or Date, with one row per day for a consistent timeline. Its fields can form a hierarchy from Year to Quarter, Month, and Date, so reports can drill from annual totals to single days. Some editors can't create hierarchies on Direct Lake tables, so check that option exists before relying on it.9. Let's practice!
Now, let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.