Skip to main content
cancel
Showing results for 
Search instead 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

Reply
erikah06
Helper I
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'Equipment_Status' Table where formula resides'Rating Matrix' Table referenced in formula'Rating Matrix' Table referenced in formula

 

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Oh, sorry, use DISTINCT instead of VALUES.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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