Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
erikah06
Helper I
Helper I

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
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

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

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

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?

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

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
Super User
Super User

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

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

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.