We have a measure that was working until today. It stopped working after a table refresh. It uses Lookupvalue and I believe it must have found duplicates because it is returning the error: “A table of multiple values was supplied where a single value was expected”. I was able to fix it by giving an alternate value of zero at the end of the lookupvalue. Is this normal behavior for lookupvalue? Is there a better solution? Here is the original expression without the zero:
Open Qty = IF(OR(CONTAINSSTRING(JOBS[JOBNO],“WO”),CONTAINSSTRING(JOBS[JOBNO],“SO”)) = TRUE(), LOOKUPVALUE(JOBDETL[QTY],JOBDETL[SO_LINE],JOBS[SO]),JOBS[Trim Qty from JOBNAME])
With zero:
Open Qty = IF(OR(CONTAINSSTRING(JOBS[JOBNO],“WO”),CONTAINSSTRING(JOBS[JOBNO],“SO”)) = TRUE(), LOOKUPVALUE(JOBDETL[QTY],JOBDETL[SO_LINE],JOBS[SO],0),JOBS[Trim Qty from JOBNAME])
Hi @michael.wescott7226
Try:
Open Qty =
IF (
OR (
CONTAINSSTRING ( JOBS[JOBNO], “WO” ), // Check if JOBNO contains “WO”
CONTAINSSTRING ( JOBS[JOBNO], “SO” ) // Check if JOBNO contains “SO”
),
LOOKUPVALUE (
JOBDETL[QTY], // Value to lookup
JOBDETL[SO_LINE], JOBS[SO] // Lookup conditions
),
VALUE ( SUBSTITUTE ( JOBS[JOBNAME], " ", “” ) ) // Remove spaces and convert to number
)
and for With Zero:
Open Qty =
IF (
OR (
CONTAINSSTRING ( JOBS[JOBNO], “WO” ), – Check if JOBNO contains “WO”
CONTAINSSTRING ( JOBS[JOBNO], “SO” ) – Check if JOBNO contains “SO”
),
COALESCE (
LOOKUPVALUE (
JOBDETL[QTY], – Value to lookup
JOBDETL[SO_LINE], JOBS[SO], – Lookup conditions
0 – Value to return if lookup fails
),
0 – Default value if lookupvalue result is blank
),
VALUE ( SUBSTITUTE ( JOBS[JOBNAME], " ", “” ) ) – Remove spaces and convert to number
)