Reply
Regular Visitor
Posts: 22
Registered: ‎11-07-2018

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

[ Edited ]

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

AlB Super Contributor
Super Contributor
Posts: 1,192
Registered: ‎11-12-2018

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

[ Edited ]

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

Regular Visitor
Posts: 22
Registered: ‎11-07-2018

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
Posts: 1,997
Registered: ‎07-10-2018

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.
Highlighted
Regular Visitor
Posts: 22
Registered: ‎11-07-2018

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. 

Regular Visitor
Posts: 22
Registered: ‎11-07-2018

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

AlB Super Contributor
Super Contributor
Posts: 1,192
Registered: ‎11-12-2018

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

Regular Visitor
Posts: 22
Registered: ‎11-07-2018

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?

Regular Visitor
Posts: 22
Registered: ‎11-07-2018

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

Regular Visitor
Posts: 22
Registered: ‎11-07-2018

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?