Help with LOOKUPVALUE

[Anonymous]

Hello everyone,

I have two tables: TAB_Summary and TAB_Projects.

TAB_Summary

Project #	Stage	Days in Stage
1	Idea	10
2	Development	15
3	Launch	20

TAB_Projects

Project #	Stage	Days in Stage
1	Idea	10
2	Idea	10
2	Development	15
3	Idea	10
3	Development	14
3	Launch	20

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,

[amitchandak]

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

[Greg_Deckler]

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