cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erikah06 Regular Visitor
Regular Visitor

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

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

 

Availabilty Error.PNG

9 REPLIES 9
Highlighted
Super User
Super User

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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 Regular Visitor
Regular Visitor

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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. 

Community Support Team
Community Support Team

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
erikah06 Regular Visitor
Regular Visitor

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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. 

erikah06 Regular Visitor
Regular Visitor

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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...

Super User
Super User

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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 Regular Visitor
Regular Visitor

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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 Regular Visitor
Regular Visitor

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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 Regular Visitor
Regular Visitor

Re: Error This can happen when a measure formula refers to a column that contains many values withou

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