Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Solved! Go to 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],", ")
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.
@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],", ")
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |