Forum Discussion
Issue Creating a relationship where the composite Key is null
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).
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-many
double 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).
Still strugaling and I appreciate your help (all of you who replied).
I have made the change to the Composite Key. It is now
CompositeKey = [Order Number] & "_" & IF(ISBLANK([CustomerId]), "NO_CUSTOMER", [CustomerId])But I still get the following Error when i try to make the relationship
When I look at the Composite keys for both tables I see no duplicates in the Order columns, Duplicates in the customer columns and no duplicates in the Composite key colums. Which to me says that a 1 to 1 should work between the 2 compositeKey. The error however shows the duplciate _NO_CUSTOMER and an actual duplicate in the USR_TRANS table which does not exist when you look at the raw data.
I am trying to see if I can upload the data files here now.
Thank you again for your help. This has been an experience so far going to power bi. 🙂Hi Mindbender
Thank you for reaching out to the Microsoft Fabric Forum Community.
Juan-Power-bi Natarajan_M krishnakanth240 Thanks for the inputs.
In addition to user points, May be the issue is that the CompositeKey still isn’t unique across both tables. Rows with missing values are being turned into the same key, such as NO_CUSTOMER, and Power BI is also finding additional duplicate keys in the USR_TRANS table. Since a one-to-one relationship requires every key to be unique on both sides, the relationship cannot be created.A good next step would be to handle blanks in both Order Number and CustomerID, then check for duplicate CompositeKey values using Group By → Count Rows in Power Query. If duplicates still show up after that, then the data likely isn’t truly one-to-one. In that case, you’ll either need to switch the relationship to many-to-one if one side can be made unique, or create a separate lookup/bridge table with distinct composite keys and connect both tables to it.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
Hi Mindbender
Thank you for reaching out to the Microsoft Fabric Forum Community.
I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.
