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

[harrinho]

Hi All, 

I have a common problem wthe LOOKUP value i want to bring to one table (let's call it Pipeline) form another (let's call it Team) and yes both include duplicates as one opportunity ID has more than one rows in the table as it might have multiple owners for example. The second (Team) table, also has more than one rows for the same Opp ID for the same reasons. 

I tried to bring in Pipeline the Full Name from Team table and apparently got the "A table of multiple values was supplied where a single value was expected" error. 

Member Name = LOOKUPVALUE(DIM_OPP_TEAM[Member Name],DIM_OPP_TEAM[opportunity_number],FACT_PIPELINE_CDL[Opp ID])

I referred to another post in this forum and tried the following 

Member Name = 
CALCULATE (
    FIRSTNONBLANK ( DIM_OPP_TEAM[Member Name], 1 ),
    FILTER ( ALL ( DIM_OPP_TEAM ), DIM_OPP_TEAM[opportunity_number] = FACT_PIPELINE_CDL[Opp ID] )
)

It works technically, meaning that it doesn't return an error, but it doesn't return the correct value. 

Is there any way to achieve the objective of the first query by getting the correct result ?

[v-danhe-msft]

Hi @harrinho,

Based on my test, you coul just use the related function:

Sample data:

Create relationship

Create a calculated column in PIPELINE_CDL table and you can see the result.

Name = RELATED(DIM_OPP_TEAM[Name])

Regards,

Daniel He

[Ashish_Mathur]

Hi,

Share some data and also show the expected result.

[Zubair_Muhammad · Accepted solution]

@harrinho

Give this a shot

Member Name =
VAR temp =
    CALCULATETABLE (
        VALUES ( DIM_OPP_TEAM[Member Name] ),
        FILTER (
            ALL ( DIM_OPP_TEAM ),
            DIM_OPP_TEAM[opportunity_number] = FACT_PIPELINE_CDL[Opp ID]
        )
    )
RETURN
    CONCATENATEX ( temp, [Member Name], ", " )