IF and LOOKUP for multiple values

Question

Hi,I have two tables, spend table and contract table similar to the below normalised data&nbsp;I have linked the two tables by Vendor ID and have a dim-date (calendar) table.This may be two issues, but I can't sort out how to sumarise the spend for each year and compare this year and last year spend by supplier but also, to be able to add whether or not there is a published contract, when I try to add the contract info, it duplicates the lines and similarily if i load up the contract info and try to add the spend, it duplicated the spend.&nbsp;I tried&nbsp;Contract = if(LOOKUPVALUE('Contract-Data'[Term Type],'Contract-Data'[Vendor Id],[Vendor])= "Published","Yes","No") but the error message I get is "a table of multiple values was supplied where a single value was expected"&nbsp;I tried a couple of ways of getting the spend by supplier by year in a new table, but I have deleted as it doesn't work,&nbsp;ThanksEmma

amitchandak · Answer

Anonymous , I am assuming you are trying for a new column
&nbsp;
Contract = if(MAxx(filter('Contract-Data','Contract-Data'[Vendor Id] =[Vendor]), 'Contract-Data'[Term Type]) = "Published","Yes","No")
&nbsp;
if needed
refer 4 ways to copy data from one table to anotherhttps://www.youtube.com/watch?v=Wu1mWxR23jUhttps://www.youtube.com/watch?v=czNHt7UXIe8

anonymous · Answer

Hi, thanks, that has got rid of the error, but all the responses are "no" when there definately should be some "yes"

amitchandak · Answer

Anonymous , Can you share sample data and sample output in table format?

smpa01 · Answer

Anonymous&nbsp; any chance you can provide some sample data?

anonymous · Answer

I can't work out how to attach an excel spreadsheet to share the data

Forum Discussion

IF and LOOKUP for multiple values

9 Replies

Related content

Return multiple values from lookup

Lookup values from multiple tables

Dax to lookup from multiple values

Return multiple values from lookup

Lookup value - error multiple values was supplied