Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Yuu are a scholar and a gent sir.
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |