DAX A table of multiple values was supplied where a single value was expected

Question

I am trying to return the respective rating that a particular value in a column falls within. For example, if the 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 field referenced in the formula which distinguishes 2 types of assets.

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

Screen Shot of Data View of Formula Location in 'Equipment_Status' Table

Screen Shot of Data View of 'Rating' Table

Alexis Olson Alexis Olson · Accepted Answer · 2019-02-22T19:03:00

This error message is usually related to the VALUES function. Since this function can return multiple values (all the existing values in the local filter context) but measures can only output a single value, you get an error in the cases where it does return more than one value.

In the case where there are multiple values, you need to decide which one you want to pick or how to aggregate them. You could take a MAX or MIN, SUM or AVERAGE, or you could even CONCATENATEX them all into a single string.

DAX A table of multiple values was supplied where a single value was expected

1 Answers