Forum Discussion
Issue Creating a relationship where the composite Key is null
Hi there, I am a complete noob with Power BI and have been struggling all day with the relationship.
I have 6 tables with relationships and 5 work. The one that doesn't has 2 fields in one table that are custom:
Custom 1
- which strips out part of a field to get the piece I need (this works fine)
=
VAR PipePos = SEARCH("|", [INVCONFIRMATIONNUMBER], 1, 0)
RETURN
IF(
PipePos > 1,
LEFT([INVCONFIRMATIONNUMBER], PipePos - 1),
[INVCONFIRMATIONNUMBER]
)
Custom 2 - which does simialr to Custom 1 and is working fine.
VAR FullText = [INVCONFIRMATIONNUMBER]
VAR FirstPipe = SEARCH("|", FullText, 1, 0)
VAR SecondPipe = SEARCH("|", FullText, FirstPipe + 1, 0)
VAR SubStr = MID(FullText, SecondPipe + 1, LEN(FullText) - SecondPipe)
RETURN
IF(LEFT(SubStr, 8) = "CUSTOMER", SubStr, BLANK())When I create a composite key of these I do not get blanks but I get 1 duplicate row.
The composite key for the second table is made up of
=[OrderID]&"_"&[CustomerID]
When I create the relationship I get cannot create a one to one relationship (or any for that matter) because there are duplicate values " in the Second tables data.
I have tried, coalesce, not ISBLANK. trying to make tables of non null data out of them but there always seems to be null data.
Any advice woudl be sp appreciated.
Thanks
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).
7 Replies
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).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
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
Hi Mindbender ,I have three questions:
1) Which tables need to filter which other tables? This is critical information based on the use case. We typically avoid bidirectional filtering in most semantic models because it can lead to ambiguous results.
2) What difference do 1:1 and 1:* relationships make in your model? Why is a 1:1 relationship necessary, and what specific use case does it solve?
3) Could you please share some sample data so we can better understand the issue you are facing?
Thanks
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.
For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan
Cheers!
