Relationship fail when adding column from another table (where relationship seems to be correct)

Hi,

I've had a good hunt around, and can't find this problem anywhere, apologies if it has been posted already.

I have a table containing data aggregated by a number of dimensions in Power BI desktop. I have created a lookup table to allow me to sort my dimensions how I choose, rather than simply in alphabetical order, when creating visualisations. However, when I try and add my sort order field from the lookup table to the main data table I get the error:

"A single value for column 'value' in table 'Query1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

When I check Manage Relationshsips, the relationship is created and active, and if I create a table or matrix in the visualisations view, I can clearly see that the relationship between the main data table and the look up table exists and is working as expected. It is only when I try to add the new column in order to sort by it that I get the error.

Any suggestions are greatly appreciated.

Thanks in advance

Robbie