IF and LOOKUP for multiple values

[Anonymous]

Hi,

I have two tables, spend table and contract table similar to the below normalised data

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.

I tried 

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"

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,

Thanks

Emma

[v-luwang-msft]

Hi @Anonymous ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[Anonymous]

unfortunately not, but as my work won't let me use google drive, i can't upload the files, only a picture of the tables included below

[v-luwang-msft]

Hi @Anonymous ，

The reason for your problem is that your contract table has duplicate data and returns multiple statuses when vendor id=173153. As shown in the red line below, eliminating the duplicate data from the table will resolve the error.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[smpa01]

@Anonymous  any chance you can provide some sample data?

[amitchandak]

@Anonymous , I am assuming you are trying for a new column

Contract = if(MAxx(filter('Contract-Data','Contract-Data'[Vendor Id] =[Vendor]), 'Contract-Data'[Term Type]) = "Published","Yes","No")

if needed

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

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

[amitchandak]

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

[Anonymous]

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

[smpa01]

@Anonymous  please upload in 1/g drive and share the link here and mention the desired end result too.