Defining Relationship Cardinality
A sales report appears to work. Selecting a customer changes the revenue total, product categories show plausible values, and the latest refresh completed. Is that enough evidence that the model’s relationships are correct?
Not necessarily. A relationship can produce believable results for ordinary data while hiding duplicate keys, mismatched business grain, or many-to-many behavior that no one intended. The real test is whether the cardinality matches what each row represents and whether filters produce the expected results under both normal and unusual conditions.
The Power BI semantic model contains the tables, relationships, metadata, and calculations used by reports. Relationship design belongs primarily in this model layer. You might use Power Query to clean or reshape data before it enters the model, and you might observe the consequences in a report or a Data Analysis Expressions calculation. Still, changing a visual doesn’t repair an incorrect model relationship. You first need to identify the layer that owns the behavior.
This distinction matters for the PL-300 course blueprint. The published skill in scope asks you to define a relationship’s cardinality and cross-filter direction. Those ideas work together, but they aren’t the same.
Cardinality describes uniqueness on each side of the relationship. Cross-filter direction affects how filters propagate along the relationship path. This episode concentrates on the cardinality decision while keeping that connection in view.
Consider a Customers table and a Sales table. The Customers table has one row for each customer. Its Customer ID identifies a customer uniquely. The Sales table has one row for each sales transaction, so the same Customer ID can appear repeatedly when a customer makes several purchases.
Relating those columns produces a one-to-many relationship. Customers is the one side because each Customer ID should occur there once. Sales is the many side because one customer can be associated with many sales rows.
The words one and many don’t describe which table is physically larger. A customer table could have millions of rows, while a filtered sales table contains only a few thousand. It would still be the one side if its relationship key is unique. Cardinality is about the pattern of values in the columns that participate in the relationship, interpreted according to the business grain. Table size alone tells you nothing decisive.
Grain means what one row represents. In Customers, one row represents one customer. In Sales, one row represents one transaction.
Once you can state those two grains clearly, the expected cardinality becomes easier to justify. One customer can have several transactions, while each transaction in this design refers to one customer. The relationship expresses that business structure in the semantic model.
Now imagine that Customers contains two rows with Customer ID four thousand and twelve. One row came from the billing system and another from a support system. The names are slightly different, but both records represent the same business customer.
Power BI can’t treat that relationship column as a valid one side while the value is duplicated. If the model previously relied on uniqueness and a later refresh introduces the duplicate, the relationship can become invalid or the refresh can fail. A report that worked yesterday doesn’t prove that tomorrow’s data will preserve the required uniqueness.
The right response isn’t to select many-to-many merely because the interface offers it. First determine why the duplicate exists. Perhaps Power Query should produce one governed customer row from the two source records.
Perhaps the records actually represent separate customer accounts and the proposed Customer ID isn’t the real unique key. Those are different business explanations. Cleaning an accidental duplicate and redefining the table’s grain aren’t interchangeable actions.
This is why automatically detected cardinality is a proposal to validate, not a substitute for model design. Power BI can inspect the available values, but a sample of current values doesn’t define the business requirement. A column might happen to be unique today even though repeated values are allowed tomorrow. Conversely, apparent duplicates might expose a data-quality problem rather than prove that many-to-many is appropriate. Validate both uniqueness and grain before accepting the detected relationship.
A one-to-one relationship requires uniqueness on both sides. Suppose one table contains one row per employee with an Employee ID, while a second contains one row per employee profile and uses that same unique Employee ID. If neither side repeats an ID, the relationship has one-to-one cardinality. That description says something precise about uniqueness. It doesn’t merely mean that the tables currently have the same number of rows.
Equal row counts aren’t sufficient evidence. Two tables can each contain ten thousand rows while repeating some keys and omitting others. They can also contain unique keys that don’t all match. Cardinality describes uniqueness on the relationship columns, not a promise that every value has a corresponding row on the other side. To validate one-to-one, profile the actual key values on each side and confirm that the proposed grain really is one row per entity in both tables.
One-to-one also shouldn’t be chosen just because two tables sound closely related. An Employees table with one row per employee and a Training table with one row per completed course aren’t one-to-one. Employee ID will repeat in Training when an employee completes several courses. The business grain leads to one-to-many, even if the report treats employee and training information as parts of the same subject.
Many-to-many applies when the relationship columns can contain repeated values on both sides. Imagine one table recording sales allocations by campaign and another recording campaign targets by region. If the proposed relationship column is Region, both tables may contain many rows for the same region. Neither column provides a unique one side. Declaring many-to-many acknowledges that structure, but it doesn’t explain what the business wants the resulting matches and filters to mean.
That semantic question is essential. If several sales-allocation rows for a region correspond to several target rows for that same region, a report calculation can behave differently from a model in which a unique region row organizes the relationship. The supplied requirement might genuinely call for direct many-to-many behavior, or it might reveal that the tables have been related at an overly broad grain. You need a deliberate explanation for the relationship rather than an emergency setting chosen to make a warning disappear.
Return to the customer report. A business user says that selecting one customer usually works, but selecting a customer that exists in both source systems produces an unexpected total. Start by reproducing that exact condition.
Record the customer selected, the expected revenue, and the value the report actually displays. Then compare it with a customer whose key is known to be unique. That gives you a normal case and an edge case instead of a vague report that sometimes looks wrong.
Next, inspect the relationship and the columns it uses. If Customers is intended to be the one side, test whether Customer ID is unique in the query output that actually reaches the model. Don’t infer uniqueness from a source-system promise or from a short preview. Check the refreshed data at the relevant stage. If the repeated key is already present in Power Query output, that’s evidence from the working solution that the model isn’t receiving a valid unique customer key.
Then restate the grain. Does each Customers row represent a governed customer, a source-system customer record, a billing account, or a customer location? Two records with the same displayed name aren’t automatically duplicates, and two records with the same proposed key aren’t automatically safe to merge. The business meaning determines whether you must correct the data, choose a different key, or redesign the relationship around a different grain.
Suppose investigation confirms that the two rows really are duplicate representations of one governed customer. The correction belongs before or within model loading so that Customers once again produces one row for that customer. After correcting the cause, refresh the model and verify that the relationship remains valid. Then repeat both report tests. The formerly duplicated customer should now produce the expected result, and the ordinary customer should continue to behave correctly.
That last test matters because a correction can fix one symptom while changing another result. Successful configuration only proves that Power BI accepted the configuration at that moment. It doesn’t prove that totals, filters, or future refreshes satisfy the requirement. Correctness must be observed in the layer where the behavior appears. For a relationship problem, useful evidence includes query output, key uniqueness, relationship state, calculation results, report filtering, and refresh behavior.
Now consider a different failure. An analyst sees repeated Region values in two tables and changes the relationship to many-to-many. The report opens, so the change seems successful.
Yet a region selection produces a target total that the business doesn’t recognize. Changing visual formatting won’t resolve the issue because the symptom comes from model structure and filter propagation, not presentation. The analyst must examine what each row represents and whether Region is an appropriate relationship grain.
If one table’s rows represent individual sales allocations and the other’s represent several target categories per region, repeated Region values are expected. The question is whether relating those repeated columns directly expresses the intended analysis. If it doesn’t, choosing many-to-many has only converted a uniqueness error into a semantic problem. The model is technically configurable, but the report still lacks a defensible meaning.
Testing should use values that expose the structure. Choose a region with multiple rows in both tables and capture the expected target according to the business definition. Apply the filter and compare the actual result. Then test a region with only one relevant row on one side, if the governed data contains such a case. A model that looks correct only where values happen to be unique hasn’t demonstrated correct many-to-many behavior.
Data Analysis Expressions, or DAX, can make relationship errors visible because model calculations are evaluated under filter context. If a report selection propagates through a relationship path differently from what the modeler intended, a measure can return an unexpected result even when its formula hasn’t changed. That doesn’t mean every surprising measure is a cardinality problem. It means relationship structure is one of the governing conditions you should inspect before rewriting a calculation that previously worked.
The same discipline applies to one-to-one. Profile both keys, verify the grain on both tables, and test report behavior with matching and nonmatching values. Don’t use one-to-one as a label for two tables that merely belong together conceptually. If either relationship column repeats, the claimed cardinality conflicts with the data. If neither repeats, you still need to confirm that the uniqueness is part of the durable business design rather than an accident of the current refresh.
Refresh testing is particularly important because cardinality depends on the values loaded into the relationship columns. A model may be valid during development when the dataset is small. A later load can introduce a duplicate on the expected one side. The resulting failure is evidence about the data contract, not merely an inconvenience to clear by relaxing the relationship. Investigate whether the new row violates the intended grain or reveals that your original assumption was incomplete.
You can organize that investigation around three questions. What does one row in each table represent? Which relationship column, if any, must be unique according to that definition? What observable report or calculation result should follow when a filter travels along the relationship path? Those questions connect the business requirement, model configuration, and consumer experience without treating any one screen as proof by itself.
Cross-filter direction remains a separate decision. Cardinality establishes where uniqueness exists, while the relationship’s filtering configuration helps determine how filters propagate. Don’t infer the full filtering behavior merely from seeing one-to-many, one-to-one, or many-to-many. Inspect the complete relationship configuration and test the resulting filter behavior. A later decision about direction should preserve the same requirement-driven approach rather than compensating blindly for a cardinality error.
This also explains why a familiar feature isn’t always the correct feature. One-to-many is common, but it’s wrong when neither side is unique. One-to-one sounds tidy, but it’s wrong when either table has repeated relationship keys. Many-to-many accepts repetition on both sides, but it requires deliberate semantics. The interface presents valid capabilities, not a universal recommendation about which one your data should use.
For an exam-style modeling requirement, avoid starting with the location of a setting. Translate the request into a model statement first. For example, each governed customer appears once in Customers, while a customer can appear in many Sales rows. That sentence predicts one-to-many cardinality and identifies which side needs uniqueness. You can then identify the key, validate the loaded values, configure the relationship, and test the expected filter result.
For one-to-one, your statement might be that each employee has at most one row under the chosen grain in each of two tables, with Employee ID unique on both sides. For many-to-many, state explicitly that the chosen relationship value may repeat in both tables and explain why that repeated-to-repeated association matches the required analysis. If you can’t explain the meaning, you aren’t ready to treat the configuration as correct.
Finally, preserve evidence from the conditions most likely to disprove your assumption. Test a duplicated key on a proposed one side, a repeated key on both sides of a proposed many-to-many relationship, and a later refresh that includes realistic data. Compare expected and actual calculation or report results. The goal isn’t simply to make the relationship dialog accept your choice. It’s to show that the semantic model continues to represent the business grain and produces behavior that users can rely on.
Cardinality is a claim about uniqueness and business grain, not about table size or whichever option Power BI happens to permit. One-to-many requires a unique side, one-to-one requires uniqueness on both sides, and many-to-many accepts repetition on both sides only when that structure has a deliberate business meaning. Before trusting any of them, state what one row represents, inspect the relationship keys, and test the resulting filters and calculations with ordinary and edge-case data. When the model survives that evidence, including a realistic refresh, you’ve moved beyond a configuration that merely opens. You have a relationship design that can be explained, tested, and connected to the report behavior the business actually requested.
Classify three relationships by cardinality before configuring them. What duplicate key or unexpected blank row would contradict your classification? For the exact feature names and practical validation steps, continue with the companion episode reference. This lesson was produced by Bare Metal Cyber dot com.