Hessam Vali - A table of multiple values was supplied where a single value was expected

Power BI
1

*can anybody help mw with this error?

This refresh was working with no problem until two days ago and then all of a sudden i got this error on the So Req date measure.

image
image1080×318 64.1 KB

The job table has 71172 lines and 71172 unique job numbers how ever, the Jobdetail table has 100540 lines and 71162 unique job numbers, however it has been always like this but for some reason the report stopped working just now and generated this error.

2

Here’s a possible solution to the error described:

The issue seems to be with the LOOKUPVALUE function used in the So Req Date measure. It’s likely that the LOOKUPVALUE is returning multiple values for a given key, where it should only return one. This can happen if there are multiple rows in the JOBTABLE with the same JOBDETAIL_SO_LINE that match the JOB(SO) value.

To resolve this, you should ensure that the relationship between the tables is correctly set up to return unique values. If the relationship is not the issue, you may need to modify the LOOKUPVALUE function to ensure it can handle or filter down to a single value. This can be done by adding additional criteria to the LOOKUPVALUE function to make sure it returns a unique row, or by using other functions such as FILTER to narrow down the result.

Here’s an approach to modify the DAX measure:

So Req Date =
VAR SingleValue = CALCULATE(
    MAXX(
        FILTER(
            JOBTABLE,
            JOBTABLE[JOBDETAIL_SO_LINE] = MAX(JOBS[SO])
        ),
        JOBTABLE[REQUIRED_DATE]
    )
)
RETURN
    IF(
        HASONEVALUE(JOBS[SO]),
        SingleValue,
        ERROR("Multiple dates found for a single SO.")
    )

In this revised measure:

  • VAR SingleValue is used to store the result of a MAXX function, which is wrapped around a FILTER function to ensure we’re getting only the rows where JOBDETAIL_SO_LINE matches the MAX of JOBS[SO].
  • MAXX is used to return the maximum REQUIRED_DATE if there are multiple rows that satisfy the condition.
  • HASONEVALUE checks if the JOBS[SO] column has one unique value for the current context.
  • If it does, SingleValue is returned, else an error message is displayed.

This is just one way to approach the problem; the actual solution might need to be adjusted based on the specific data model and business logic.

3

Thanks Sam.
I’ll give it a shot. What I cannot comprehend is that why this error just came up after using this report for quite some time with no changes in the model/ structure.

Thanks again, I’ll get back to you with more questions after testing.