Solved: “A table of multiple values was supplied where a single value was expected” Power BI

In this article, we will show you how to solve the error “A table of multiple values was supplied where a single value was expected” in Power BI.

We have created some example data to help explain why this error occurs when you are writing DAX functions and to demonstrate three ways you can resolve it.

Example

Here is some example data showing clients, the product they bought and the quantity of what they bought.

In a separate table, we have a list of the product IDs.

In this example, we want to create a new custom column in Power BI to show the customer who has bought the highest quantity of each product.

We have chosen to use LOOKUPVALUE to return the name.

To find the customer with the maximum quantity, we have created a variable to use in the LOOKUPVALUE parameters.

Top Client = 

VAR Max_Qty =

    CALCULATE(
        MAX(Orders[Quantity]),
        FILTER(ALL(Orders[Product ID]), Orders[Product ID] = 'Product'[Product ID])
    )

RETURN

LOOKUPVALUE(Orders[Client],Orders[Product ID],'Product'[Product ID],Orders[Quantity],Max_Qty)

However, when we apply this DAX formula in our custom column, it looks like this:

An error is returned with the following message:

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

See below:

Why this error occurs

This error occurs due to there being two or more of the same values per ID you are searching in your formula.

Therefore, the function does not know which one to return, since there are multiple.

In our example, for Product ID ‘1’, there are two Quantities of ‘4’.

These are the multiple values being referred to in the error message.

Solutions to the error: A table of multiple values was supplied where a single value was expected

Solution 1: Remove duplicates from table

One way to solve this error is to remove the duplicates which are causing there to be ‘multiple values’.

Of course, you may not be able to do this if it is not viable to change your data in this way.

However, if you were not expecting this error and there should not be any duplicate values in your dataset, then sorting out this issue will remove the error.

In our example, if we change one of the duplicated values in Power Query, it will remove the error, as below.

Now, there are no duplicated values for Product ID ‘1’, so the above custom column will calculate correctly and return the Client names with the highest Quantity per Product ID.

Solution 2: Use first or last non blank

We can use the DAX functions LASTNONBLANK and FIRSTNONBLANK to return values that appear first or last in the column.

We will also include a filter so it returns the first or last non blank for the highest quantities per client.

Here is the custom column we would use to return the client with the highest quantity per product ID, using LASTNONBLANK:

Top Client = 

VAR Max_Qty =

    CALCULATE(
        MAX(Orders[Quantity]),
        FILTER(ALL(Orders[Product ID]), Orders[Product ID] = 'Product'[Product ID])
    )

RETURN    

CALCULATE (
    LASTNONBLANK ( Orders[Client], 1 ),
    FILTER (ALL ( Orders ), Orders[Product ID] = 'Product'[Product ID] && Orders[Quantity] = Max_Qty))

Here are the results:

For Product ID ‘1’ it has returned the last name alphabetically. If we want to return the first name, then we would simply change LASTNONBLANK to FIRSTNONBLANK in the column.

This method may not be the best one to use if you have values that are neither first or last in the column, or you want to view all of the values at once.

Solution 3: Concatenate results

The last method to solve the issue of mutliple values is to use the CONCATENATEX function to concatenate all of the results in one cell.

This is an alternative to first or last non blank and still uses a filter to return the maximum quantity per person in our example.

Rather than choosing a value to return based on whether it is first or last, it will concatenate the results with a delimiter of your choice.

Here is the custom column we would use to return all the clients with the highest quantities per product ID:

Top Client = 

VAR Max_Qty =

    CALCULATE(
        MAX(Orders[Quantity]),
        FILTER(ALL(Orders[Product ID]), Orders[Product ID] = 'Product'[Product ID])
    )

RETURN

CONCATENATEX(
    FILTER(
        ALL(Orders),
        Orders[Product ID] = 'Product'[Product ID]
        && Orders[Quantity] = Max_Qty
    ),
    Orders[Client],", ")

Here are the results:

In our example, this method would be the most useful.