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

[Anonymous]

i'm using the formula:

scopef2 = LOOKUPVALUE (
'Table'[code],
'Table'[Project Name], actuals[Project Name])

But error is :A table of multiple values was supplied where a single value was expected

then i used

scopecode = CALCULATE (
FIRSTNONBLANK ( 'Table'[code], 1 ),
FILTER ( ALL ( 'Table' ), 'Table'[Project Name] =actuals[Project Name] )
)

but this is giving wrong output like

projectname  scopecode

att                  FN

zte                  Fn

it is giving only fn values for all projects but there are multiple values in column "code" which are not displayed

output should be:

projectname  scopecode

att                  FN

att                  TP

att                  GS

zte                 FN

zte                  TP

zte                  GS

Let me know what is wrong im doing

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Actuals:

Table:

You may create a measure or a calculated column as below.

Measure:

Measure = 
CONCATENATEX(
    FILTER(
        ALL('Table'),
        'Table'[Project Name]=SELECTEDVALUE('Actuals'[Project Name])
    ),
    [Code],
    ","
)

Calculated column:

Column = 
CONCATENATEX(
    FILTER(
        ALL('Table'),
        'Table'[Project Name]=EARLIER('Actuals'[Project Name])
    ),
    [Code],
    ","
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous - If this is a measure:

scopef2 = LOOKUPVALUE ('Table'[code],'Table'[Project Name], actuals[Project Name])

Then it would probably need to be something like this:

scopef2 = LOOKUPVALUE('Table'[code],'Table'[Project Name],MAX(actuals[Project Name]))

The reason is that references to columns generally need an aggregator around them. Now, the first two parameters of LOOKUPVALUE do not because these are simply informational to the function, where to look, what to bring back. The third however is the value you are looking up. Thus, you need to wrap it in an aggregation.

Now, the other potential problem that you might have is that you didn't specify an alternate result in the event that multiple rows are found. You can fix this like:

scopef2 = LOOKUPVALUE('Table'[code],'Table'[Project Name],MAX(actuals[Project Name]),"Alternate")

Alternatively, you can avoid that possibility by doing this:

scopef2 = CONCATENATEX(FILTER('Table'[Project Name]=MAX(actuals[Project Name])),[code],",")

In any event, sample source data would be tremendously helpful in this circumstance to be very specific about how to fix this properly.

[Greg_Deckler]

@Anonymous - Did this solve your issue?

[amitchandak]

@Anonymous , Try as new columns

maxx(FILTER ( ( 'Table' ), 'Table'[Project Name] =actuals[Project Name] ),'Table'[code])

or
minx(FILTER ( ( 'Table' ), 'Table'[Project Name] =actuals[Project Name] ),'Table'[code])

[Anonymous]

@amitchandak ,

maxx or minx are displaying single values only.

[Greg_Deckler]

@Anonymous - MAXX and MINX will always display single values. Use the alternate result parameter of LOOKUPVALUE to determine if you are getting multiple values back. Or, replace MAXX and/or MINX with COUNTROWS.