Calculate(Values) - A table of multiple values was supplied where a single value was expected

[erikah06]

I am trying to return the 1-5 scaled rating that a value in a column falls within. For example, if grade is 90< and >100 assign A. However, the formula I am using is resulting in the error. "A table of multiple values was supplied where a single value was expected."

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

This is the helper column referenced in the 'Equipment_Status' Table which distinguishes 2 types of assets for the rating scale.

Helper = RELATED('Performance Matrix'[Helper])

'Equipment_Status' Table where formula resides'Rating Matrix' Table referenced in formula

[Greg_Deckler]

It's because you are using VALUES, that returns a table. You need to use CONCATENATEX or something like that to get down to a single value being returned.

[erikah06]

@Greg_Deckler Thank you for the insight. How would I incoporate concanatex into this formula?

[Greg_Deckler · Accepted solution]

Different ways to do it, but if it were me, I would probably do something like this:

Availability Rating = 
VAR __table = 
	FILTER('Rating Matrix',
		'Rating Matrix'[Avaibility (L)]<='Equipment_Status'[Availability]
        	&&'Rating Matrix'[Availability (H)]>='Equipment_Status'[Availability]
		&&'Rating Matrix'[Bus Type]='Equipment_Status'[Helper])
VAR __table1 = SELECTCOLUMNS(__table,"__rating",[Rating])
RETURN
CONCATENATEX(VALUES(__table1),[__rating],", ")

[erikah06]

@Greg_Deckler  Thank you for this. I tried to use the formula, but received the error " The VALUES function expects a column reference expression or a table reference expression for argument '1'."

[Greg_Deckler]

Oh, sorry, use DISTINCT instead of VALUES.

[erikah06]

THank you @Greg_Deckler this worked perfectly! You're a life saver!