cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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

1 ACCEPTED SOLUTION
Super User

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],", ")

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

Super User

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],", ")

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

@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'."

Super User

Oh, sorry, use DISTINCT instead of VALUES.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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