Forum Discussion
Issue Creating a relationship where the composite Key is null
- 3 months ago
Isee two separate issues here
The duplicate problem — when Custom 2 returns BLANK(), your composite key becomes something like "ABC123_" (just the first part with nothing after the underscore). If multiple rows have a blank Custom 2, they all get the same composite key and Power BI sees duplicates. The fix is to handle blanks explicitly in your composite key column:
= [Custom1] & "_" & IF(ISBLANK([Custom2]), "NO_CUSTOMER", [Custom2])
Or filter out those blank rows entirely in Power Query if they shouldn't be in the relationship at all.
The one-to-one vs one-to-manydouble check whether this relationship genuinely needs to be one-to-one. If the billing transaction table can have multiple rows per order+customer combination, you need many-to-one, not one-to-one. The "duplicate values" error is Power BI telling you the key isn't unique on that side.
Start by going to Power Query, adding a column that counts duplicates of your composite key, and seeing how many rows actually have dupes. That'll tell you if it's a data quality issue (unexpected blanks) or a modeling issue (the relationship cardinality is wrong).
Hi Mindbender
It is not due to nulls where composite key is not unique. Power BI need one side of a relationship to have distinct values and even a single duplicate will prevent it. This can happen due to duplicates, hidden blanks or having spaces for the fields.
You can validate the key using DISTINCTCOUNT, normalize it with TRIM or UPPER and ensure no blanks exist. If duplicates are valid then 1 to 1 or 1 to many relationship will not work where you will need to switch to many to one relationship or create a separate dimension table with unique keys