Identifying and Creating Relationship Keys
A sales report appears to work. A product slicer changes the revenue total, the charts look plausible, and Power BI accepted the relationship. Is that enough evidence that the relationship uses the right keys?
Not quite. A model can behave correctly for the few products you happen to test and still fail when a duplicate code arrives, a source system changes a code, or two columns that look alike contain incompatible values. The stronger question is whether the keys represent the same business entity, have compatible data types, and satisfy the required uniqueness on the one side of the relationship.
That question sits directly inside the PL-300 skill to identify and create appropriate keys for relationships. The emphasis is on appropriate. A key isn’t merely a column that Power BI lets you connect to another column. It’s a value, or a designed identifier, that establishes which row in one table corresponds to rows in another. Choosing one requires you to understand what each table represents and the grain of each table, meaning what one row stands for.
Consider a governed sales model with a Product table and a Sales table. Each row in Product is intended to represent one product. Each row in Sales represents a sales event or another defined sales-level record. The Product table might contain Product Key, Product Name, Category, and Brand.
The Sales table might contain many rows with the same Product Key because one product can appear in many sales records. That repetition is expected on the many side. In Product, however, the intended one-side key must be unique. If Product Key repeats there, the table no longer provides one unambiguous product row for each key.
The first requirement is matching meaning. Suppose Product contains an internal Product Key, while Sales contains a supplier’s catalog number. Both columns might contain numbers, and some values might even coincide, but they don’t necessarily identify products in the same system. Connecting them because their samples look similar would confuse compatible appearance with common meaning. Before creating the relationship, you need to establish that a value in the Sales key refers to the same product represented by that value in Product.
The second requirement is compatible data type and formatting. Imagine that Product stores its identifier as a whole number, while the corresponding Sales field arrives as text. Or perhaps both are text, but one source adds formatting that changes the actual values. A key displayed as zero zero four two isn’t automatically equivalent to numeric forty-two.
The correct cleanup depends on what the identifier means. If leading zeroes are meaningful parts of a code, converting it carelessly to a number could destroy identity rather than standardize it. The goal isn’t to make the columns look convenient. It’s to give corresponding identifiers compatible representations without changing their business meaning.
Power Query is Power BI’s data acquisition and transformation environment. It’s often the appropriate place to correct source formatting, assign compatible types, remove unintended formatting differences, or construct a prepared key before data reaches the semantic model. Those changes affect the query output produced during refresh.
The relationship itself belongs to the semantic model, where Power BI uses cardinality and cross-filter direction to determine how filters propagate along relationship paths. A visual can reveal the result of that behavior, but changing a visual doesn’t repair an invalid key. A Data Analysis Expressions calculation can respond to model filter context, but it doesn’t turn two unrelated identifiers into a trustworthy relationship.
This separation helps you avoid a familiar mistake. If the failure begins with inconsistent source values, repair or standardize those values during preparation. If the prepared columns are sound but the relationship has the wrong cardinality or direction, inspect the semantic model.
If the model behaves correctly but one visual doesn’t respond as expected, then the report interaction may deserve attention. If access differs between consumers, the service or security context may matter. Starting with the layer that owns the behavior prevents a series of unrelated changes that hide the original defect.
Now test uniqueness on the intended one side before accepting the relationship. For the Product table, compare the number of product rows with the number of distinct candidate key values, or otherwise profile the candidate key for duplicates. If the candidate key repeats, investigate why. Two rows might be accidental copies.
They might represent different products that share a code. They might represent multiple versions of one product. Those causes aren’t interchangeable, so deleting duplicates without understanding the grain can produce a tidy-looking table that has lost legitimate information.
Suppose Product Code P one hundred appears twice. One row describes a blue product and the other describes a red product. If color is part of the product’s identity, Product Code by itself isn’t unique at the table’s grain.
The real business identifier may be a combination of Product Code and Color, or the source may provide a different identifier that distinguishes the products. A relationship on Product Code alone would treat two distinct product rows as if they had one identity. The interface’s willingness to show relationship options doesn’t make that design appropriate.
Composite business keys create another decision. Imagine that a branch system identifies products with both Branch Code and Local Product Code. Local Product Code seven may exist in several branches, so neither column alone identifies a product across the combined dataset. Together, the two values may be unique.
You could prepare a composite representation carefully, but you must preserve both meaning and data type rules. An alternative is a surrogate key, which is a model-oriented identifier assigned to represent the entity. Surrogate keys often provide stable joins when business keys are composite or can change.
A surrogate key doesn’t excuse weak data preparation. The same assigned identifier must reach the corresponding rows on both sides of the relationship. If the Product table assigns surrogate Product Key eight hundred and four to Branch North, Local Product seven, the matching Sales rows must receive that same key through a reliable preparation or mapping process. Generating unrelated row numbers in Product and Sales wouldn’t establish identity. The number becomes useful because it consistently represents the same product, not because it happens to be numeric.
Stability matters because a business code can be unique today and still be a poor long-term relationship key. Suppose a product’s public stock code changes after a rebranding. If historical Sales rows retain the old code while Product contains only the new code, a direct relationship on that mutable code can stop matching history to the product.
A stable surrogate identifier can preserve the modeled identity while descriptive or operational codes change. This isn’t a rule that every business key must be replaced. It’s a reason to ask whether the candidate key remains consistent for the lifecycle and history the model must support.
The nearest alternative might be a genuinely stable business key that the organization governs as unique and immutable for the model’s purpose. If that key has the same meaning in both tables, uses compatible representations, and is unique on the intended one side, it may be entirely appropriate. There isn’t one universal setting or key style for every Power BI model. The decision follows the entity, grain, source behavior, and reporting requirement. A familiar feature is not a substitute for those facts.
Return to the sales model after refresh. A normal test uses Product P one hundred, which has clean matching rows in both tables. Selecting that product filters Sales and produces the expected total.
It’s tempting to stop there. But Product P one hundred only proves that one ordinary value followed the relationship path as expected. It says nothing about a duplicate product key, a text value with inconsistent formatting, an unmatched code, or a code that changed between periods.
A stronger validation starts with prepared query output. Confirm that the candidate Product key is unique at the intended Product grain. Inspect whether Product and Sales keys have compatible data types and normalized representations. Check representative values from each source, including known edge conditions.
This establishes whether the relationship is receiving suitable inputs. A successful refresh only proves that processing completed. It doesn’t prove that the resulting identifiers carry the correct meaning or that every expected Sales row can find its Product row.
Next, validate the semantic model’s behavior. The Product table is intended to supply the one side and Sales the many side. With the relationship configured for the required behavior, filters propagate along the relationship path according to its cardinality and cross-filter direction.
Select a known product and compare the filtered result with the expected sales records. Then test a category containing several products. This checks more than one isolated match because the category filter must first select the relevant Product rows and then affect related Sales rows through the model.
Now reproduce an edge condition deliberately. Suppose a new source extract adds another Product row with an existing Product Key. The report might still show plausible totals for products unaffected by that duplicate. Your quality check should detect that the intended one-side key has lost uniqueness before you trust the report.
The correction depends on the cause. If the second row is an accidental duplicate, repair the preparation logic or source issue. If it represents a legitimate distinct entity, revise the grain or choose a key that distinguishes the two entities. Simply forcing a relationship configuration would avoid the real modeling decision.
Consider a different failure. Product Key is stored as text in both prepared tables, but one system supplies a code with formatting that the other system doesn’t use. A product slicer then fails to affect the expected Sales rows. The relationship configuration may be unchanged and technically present, yet those values don’t correspond.
Reproduce the failure using that specific product, compare the prepared key values, correct the formatting at the query layer without damaging meaningful characters, refresh, and repeat the same filter. Then retest a product that worked before. The first retest proves that the identified defect was corrected. The second helps confirm that the cleanup didn’t break the normal case.
This is why verifiable evidence must come from the layer where the behavior occurs. Query profiling and prepared outputs tell you about uniqueness, type, and formatting before the data enters the model. Relationship behavior tells you whether filters reach the intended rows. Calculation output can show the downstream effect of that filter context.
Report interactions reveal what a consumer experiences. Refresh and access states answer different questions again. No single green check mark establishes correctness across all of those layers.
Imagine that totals are right when the report opens, but wrong after a user selects Category A. That symptom directs attention toward filter behavior, not merely the grand total. First state the expected result. Category A should select its Product rows, and those rows should filter the related Sales records through the relationship path.
Then observe the actual result with that filter active. If unrelated Sales remain included, inspect the model relationship and the keys that support it before rewriting calculations or rearranging visuals. Cardinality and cross-filter direction govern propagation, while the keys determine which rows can correspond.
There’s an important distinction between a relationship that exists and a relationship that expresses the correct business structure. Connecting two transaction tables through a repeated order number may create a very different cardinality from connecting a dimension table, with one row per entity, to a fact table containing repeated references to that entity. The right design begins with row meaning and grain. Uniqueness isn’t an arbitrary restriction imposed after modeling. It establishes whether the supposed one-side table can actually identify one entity row for each key.
The same reasoning protects Data Analysis Expressions results. A calculation can be perfectly valid and still return an unexpected number because the model delivered an unexpected filter context. If two keys don’t correspond, the intended Sales rows may not be selected. If the one-side table contains unresolved duplicate identities, the dimension no longer provides the clean entity selection the model expects. Before blaming the expression, verify the prepared values, uniqueness, relationship definition, and active filter state that feed the calculation.
A governed solution also needs repeatable checks, not a one-time visual inspection. Today’s data may satisfy uniqueness even though tomorrow’s refresh introduces a reused code. A source may change a field from a number to text, or start supplying a different representation. A stable design anticipates that key quality is a continuing data condition.
Validation should therefore include the refreshed query output and the resulting model behavior, especially for business keys known to be composite or mutable. The point isn’t to assume failure. It’s to ensure that a later source change can’t silently redefine identity.
When a defect appears, resist changing several layers at once. Record the expected and actual behavior under a controlled condition. Determine whether the input values are wrong, the intended one-side key isn’t unique, the relationship is configured incorrectly, or the report is presenting a separate interaction issue.
Correct the direct cause and repeat both the failing condition and a known good condition. That sequence produces evidence about what changed. If you alter the query, relationship, calculation, and visual together, a plausible final chart won’t tell you which change mattered or whether another side effect remains.
For the sales model, the evidence now forms a connected chain. Each Product row has the intended grain. Its relationship key is unique on the one side. Sales carries a corresponding key with the same meaning and a compatible type.
Any composite or mutable source identity has been handled in a way that preserves stable correspondence. The semantic model uses the relationship to propagate filters according to its cardinality and cross-filter direction. Finally, controlled filters produce expected results for ordinary products and edge conditions after refresh.
That chain is much stronger than saying Power BI accepted the relationship. It explains why the key is appropriate, where its values were prepared, how the model uses it, and what a report consumer can observe. It also gives you a practical order for troubleshooting. Begin with entity meaning and grain, test uniqueness and compatibility, inspect the relationship mechanism, and verify the downstream result. A button path may help you create a relationship, but this evidence tells you whether the relationship deserves to remain in the model.
An appropriate relationship key does more than connect two columns. It preserves the identity of the same entity across tables, uses compatible values, and remains unique on the intended one side. When business identifiers are composite or likely to change, a properly mapped surrogate key can provide the stability the model needs. Then the relationship can propagate filters according to its cardinality and direction.
The practical test is evidence, not configuration success. Check the prepared keys, confirm uniqueness, exercise a known filter, and retest an edge condition after the correction. When those results agree with the table grain and business requirement, you can explain not only that the relationship works, but why it’s trustworthy.
Design a relationship key that is stable, unique on the one side, and compatible across tables. What duplicate, blank, or formatting condition would you test first? The episode reference preserves the non-narrated details needed for practice and later recall. This lesson was produced by Bare Metal Cyber dot com.