Error This can happen when a measure formula refers to a column that contains many values without.

[erikah06]

Hello, I am in Desktop and hit "New Column" and entered the following formaula

Availabilty Rating = CALCULATE(VALUES('Rating Matrix'[Rating]),filter('Rating Matrix','Rating Matrix'[Avaibility (L)] <= 'Equipment_Status'[Availabilty)] &&'Rating Matrix'[Availability (H)]>='Equipment_Status'[Availabilty]&&'Rating Matrix'[Bus Type]='Performance Matrix'[Helper]))

and received the following error message

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

[v-frfei-msft]

Hi @erikah06,

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.

Regards,

Frank

[erikah06]

Hi @v-frfei-msft here is the sample data where I am trying to calculate the Availabitlity Rating

https://ridemetro-my.sharepoint.com/:u:/g/personal/ea94_ridemetro_org/EWqsftJVnwJEqPEh9P036nABxFJmDE...

[AlB]

Hi @erikah06

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

[erikah06]

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?

[erikah06]

Hi @AlB & @v-frfei-msft if you send me your email, I can share with you and you will not have to enter a password

[erikah06]

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. 

[AlB]

Hi @erikah06

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( )  

[erikah06]

@AlB Can I add row context to the "Equipment Status" page by using filter a second time within the formula?

[erikah06]

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.