02-08-2019 09:52 AM - edited 02-08-2019 09:57 AM
Hello, I am in Desktop and hit "New Column" and entered the following formaula
A single value for column 'Availability' in table 'Equipment_Status' 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.
I saw previous posts with this error and the resolution was the user was attempting to create a measure, but I am hitting "New Column" and still receiving the same message.
I included the screen shot of the type of field [Avaibility] is
02-08-2019 10:06 AM - last edited Wednesday
That means you don't have a row context for 'Equipment_Status'[Avilability] and therefore a single value cannot be determined when you reference the column.
In what table are you creating this calculated column? You will only have row context for:
1. that table and
2. 'Rating Matrix' within FILTER( )
02-08-2019 02:00 PM
Hi @AlB I am creating the column in the Performance Matrix table, I tried to merge the Availabilty column in the Performance Matrix table, but it is at the desktop level and not at the query level and can not retrieve.
02-10-2019 07:04 PM
I think you should updating your formula like this. You will not get this kind of error by adding MAX or SUM before the column.
Availabilty Rating = CALCULATE(VALUES('Rating Matrix'[Rating]),filter('Rating Matrix','Rating Matrix'[Avaibility (L)] <= 'Equipment_Status'[Availabilty)] &&'Rating Matrix'[Availability (H)]>=MAX('Equipment_Status'[Availabilty])&&'Rating Matrix'[Bus Type]='Performance Matrix'[Helper]))
If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
02-11-2019 08:44 AM
Hi @v-frfei-msft I received the following error "Column 'Availabilty' in table 'Equipment_Status' cannot be found or may not be used in this expression."
I will upload file shortly.
02-11-2019 09:13 AM
Hi @v-frfei-msft here is the sample data where I am trying to calculate the Availabitlity Rating
02-11-2019 09:48 AM
That file link requires a password.You can upload your file to tinyupload.com for instance (no sign-up required) and share the url here
02-11-2019 09:59 AM
Hi @AlB that website is blocked for me.
Is there a way I can take the password protection off of the file in One Drive?
02-11-2019 10:34 AM
02-11-2019 11:11 AM
@AlB Can I add row context to the "Equipment Status" page by using filter a second time within the formula?