Power BI relationship cardinality with a duplicate-key example

Relationship cardinality describes how keys repeat across related tables. In a conventional customer-to-sales relationship, each customer key appears once in the customer dimension and can appear many times in the sales fact. Repeated customer keys in sales are expected; repeated keys on the dimension's one side violate that model contract.

Do not switch to many-to-many simply because a duplicate key prevents a one-to-many relationship. First establish whether the repetition is a genuine business relationship, a historical version or a data-quality defect.

Inspect the intended one and many sides

The Power BI retail lab has six DimCustomer rows with unique CustomerKey values 0 through 5. FactSales contains eight lines, several belonging to customer 1. This is a valid one-to-many pattern.

CustomerID is a descriptive business identifier stored as text; CustomerKey is the model relationship key. The explicit key 0 represents Unknown. It is a single dimension member, not several repeated blank customer rows.

Create the relationship with DimCustomer on the one side and FactSales on the many side. Microsoft explains cardinality and relationship behaviour in its model relationship documentation.

Introduce a controlled duplicate

In a copy of DimCustomer.csv, add a second row with CustomerKey 2 and Region East, while the original row has Region West. The customer key now points to two conflicting descriptions.

A one-side uniqueness check should fail before the model is accepted. Depending on when the change is introduced, Power BI may reject relationship creation or surface a refresh/model error. Record the actual behaviour in your version rather than treating the precise dialog wording as part of the data contract.

The lab's reference checks validate the clean keys and amounts in SQLite. They do not execute this Power BI mutation; it is an explicit application acceptance test.

Determine why the key repeats

If both rows are identical deliveries, a controlled replay-removal rule may be appropriate. If they disagree without version evidence, the source requires resolution. If they represent West before a move and East after it, the table is historical and needs effective-date or version-key modelling.

Adding an arbitrary index column to each duplicate row makes row identifiers unique, but it does not tell a sales line which version is correct. The fact must reference the intended dimension version through a justified transformation.

If the real relationship permits several categories per customer, model that association explicitly, often with a bridge and carefully defined measures. It is a different business question from one authoritative customer region.

Understand why totals alone are insufficient

The clean fixture's paid total is 69,500 paise. Customer 2 contributes 20,000, all assigned to West under the clean dimension.

A flawed relationship design can produce confusing regional attribution while an overall fact-table sum remains unchanged. Therefore validate customer and region slices as well as the grand total. Inspect which fact keys enter each slice rather than accepting a total card as proof of relationship correctness.

Do not assume a semantic-model relationship behaves exactly like physically merging and expanding rows in Power Query. Both require correct keys, but their query and aggregation behaviour differ. Test the actual model and visual context.

Preserve unmatched facts visibly

An unknown customer does not require a duplicate dimension key. The fixture maps unresolved identity to one explicit Unknown member, retaining its 5,000 paid paise.

Check for fact keys absent from the dimension and choose a documented handling policy. A business report should show the unresolved population rather than quietly losing its value or assigning it to a convenient known customer.

Use an acceptance table

Record dimension row count, distinct key count, blank-key count and orphan fact-key count. Then compare paid amounts by each relevant dimension member. For the clean fixture, North is 44,500, West 20,000 and Unknown 5,000.

Repeat those checks after every dimension update. A model that was valid when first built can fail when a later source file introduces a duplicate or changes key types.

Exercise: create three mutations: an identical customer replay, a conflicting region for the same key and a new fact key absent from the dimension. Write a different resolution policy for each instead of treating all three as a cardinality-setting problem.

NeuraPath's Data Analytics with Generative AI course connects relationship design with data-quality reasoning. A convincing model review explains why its cardinality is valid and how future source changes are checked.

Continue learning

This article is part of the Power BI data models and reporting sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Power BI star schema: define facts and dimensions first.
• Continue with Single versus bidirectional filtering in Power BI.