Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
harrinho
Helper III
Helper III

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

 

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 ?

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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], ", " )

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
v-danhe-msft
Employee
Employee

Hi @harrinho,

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

Sample data:

1.PNG

Create relationship

3.PNG

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

Name = RELATED(DIM_OPP_TEAM[Name])

5.PNG

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

 

Share some data and also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Zubair_Muhammad
Community Champion
Community Champion

@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], ", " )

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.