Disctint Union won't allow relationship due to non-Unique (Distinct) values?

[DataUsurper]

Hello,

I have the following table formula for a distinct value from three separate tables:

keyMeasures = 
DISTINCT(
UNION(
SELECTCOLUMNS('Availability Top 10K',"keyCombo",'Availability Top 10K'[Combo Code]),
SELECTCOLUMNS('Revenue Top 10K',"keyCombo",'Revenue Top 10K'[Combo Code]),
SELECTCOLUMNS('Utilization Top 10K',"keyCombo",'Utilization Top 10K'[Combo Code])
))

but when I attempt to link a table's value to that new keyCombo field it is telling me there are duplicate values in both fields. I've confirmed (copy/pasting above results to Excel) that that above code does not produce a duplicate value. Any ideas why I'm getting this error?

Thank you much

[MattAllington]

UNION in DAX is the same as UNIONALL in SQL. wrap the whole thing inside a DISTINCT()

[DataUsurper]

Hi Matt and thanks for the quick reply,

The formula begins and is wrapped in a Disctinct() function, at least I believe it is. I thought maybe if I removed the union it might work but nay, the formula wouldn't complete w/out it.

[MattAllington]

Oh, sorry. The issue is most likely therefore different data in the non-primary key columns. Cna you use Power Query instead?  Select remove duplicates. 

---

---

[DataUsurper]

The data contains daily records, so there will be a lot of instances of the same codes.

This is my current model layout without any of the relationship errors, leading me to believe it's in the creatd "keyCombo" formula we're working on.?.

[MattAllington]

What is the purpose of the to-be red relationship?

[DataUsurper]

Creating a unique list of records (key) from multiple tables' fields so I don't have to create one in Excel

[MattAllington · Accepted solution]

Why do you need a relationship if you’re objective is to create a unique list of keys?  Or are you just using that to validate that this list is unique?  Check it doesn’t have any blanks, as you can’t create a relationship if there is a blank on the primary key. 

---