Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help with LOOKUPVALUE

Hello everyone,

 

I have two tables: TAB_Summary and TAB_Projects.

 

TAB_Summary

Project #StageDays in Stage
1Idea10
2Development15
3Launch20

 

TAB_Projects

Project #StageDays in Stage
1Idea10
2Idea10
2Development15
3Idea10
3Development14
3Launch20

 

I would like to obtain the Days In Stage value of TAB_Projects corresponding to the stage that the project in TAB_Summary is in.

 

For this, I thought of creating a calculated column using LOOKUPVALUE

 

 

Days in Stage = LOOKUPVALUE(TAB_Projects[Days in Stage],TAB_Projects[Project #],TAB_Summary[Project #],TAB_Projects[Stage],TAB_Summary[Stage])

 

 

But I'm getting the following error: "A table of multiple values was supplied where a single value was expected"

 

Any idea how to solve this problem?

 

Best Regards,

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - Hard to be certain. The error you are getting is generally encountered when creating a measure and referencing columns without an aggregator. You can try the MAXX(FILTER(...),...) approach:

     

     

    Days in Stage = MAXX(FILTER(TAB_Projects, TAB_Projects[Project #] = TAB_Summary[Project #] && TAB_Projects[Stage] = TAB_Summary[Stage]),TAB_Projects[Days in Stage])

     

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , a new column in the summary table

    Days in Stage = sumx(filter(TAB_Projects,TAB_Projects[Project #] = TAB_Summary[Project #] && TAB_Projects[Stage] =TAB_Summary[Stage] ),TAB_Projects[Days in Stage])