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

[robbie337]

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

[greggyb · Accepted solution]

Your model diagram would be quite helpful here, but I can take a guess. You're trying to use a bare column reference in a Calculated Column:

// DAX
// Calculated Column
// Incorrect syntax
MyNewColumn =
'LookupTable'[Field]
// The above is incorrect because relationships are not evaluated by
// default in a row context.


// Correct
MyNewColumn =
RELATED( 'LookupTable'[Field] )

Ninja edit: Also, if you already have a dimension (lookup table), there's no need to bring your sort field into the primary fact table. You can absolutely should use your dimension fields as the labels and categories in visualizations.

[robbie337]

Yuu are a scholar and a gent sir.

Thank you