Use of something Like PROCV in Excel ( OR LOOKUPVALUE IN DAX )

[Anonymous]

Hey Guys !

I'm facing a little trouble in one of my projects.

Actually, I'm trying to do something simple

I Have a Table with numerous entries of data about a lots of products.
Something Like this: 

Date	Product	Product description 2	yEAR	Month
20/07/22	X	AAAA	2022	01
20/06/22	Y	BBBB	2022	01
20/05/22	Z	CCCC	2022	01
20/04/22	A	DDDD	2022	01

And other table with the costs of each product, but the costs varies per month.

Year	Month	product	Cost
2021	05	X	10
2021	05	Y	10
2021	05	Z	10
2022	05	X	14
2022	04	Y	14
2022	03	Z	14
2022	05	A	14
2022	04	X	14

I tried to create a conditional column, using LOOKUPVALUE , Something like this:

Column = LOOKUPVALUE('Cost'[Cost],'Cost'[Year],Products[Year],'Cost'[Month],Products[Month],Prodcuts[Month],'Cost'[Product],products[Product])

But, i got an error 

A table of multiple values ​​was provided, where a single value was expected.

So... Any ideias of Any idea what's going on ?

🙂

[WinterMist · Accepted solution]

@Anonymous 

Is this what you are looking for?

I don't believe you can compare Cost[Year] or Cost[Month] with Data[Date].

To make the comparison, I first had to create Year & Month columns within the Data table as well.

Hopefully this is helpful to you.

Regards,

Nathan

[Anonymous · Accepted solution]

Hey Nathan !

You are totally correct and i Did that.

The Columns that i created ( Month and Year ) was based on the data Column.

And i compared them just like I did in My formula:

Column = LOOKUPVALUE('Cost'[Cost],'Cost'[Year],Products[Year],'Cost'[Month],Products[Month],Prodcuts[Month],'Cost'[Product],products[Product])

[WinterMist]

Hello @Anonymous 

Any update?  Were you able to resolve the issue?

Regards,

Nathan

[WinterMist · Accepted solution]

@Anonymous 

If I copy/paste your code and separate it to become more user-friendly, it reads as follows:

After the first parameter (the return value), the rest of the parameters should come in pairs.  But the misspelled parameter above highlighted in red is not part of a matching pair.  I believe this should be removed.

Try that and see if it makes a difference.

Regards,

Nathan

[WinterMist · Accepted solution]

@Anonymous 

Is this what you are looking for?

I don't believe you can compare Cost[Year] or Cost[Month] with Data[Date].

To make the comparison, I first had to create Year & Month columns within the Data table as well.

Hopefully this is helpful to you.

Regards,

Nathan

[Anonymous · Accepted solution]

Hey Nathan !

You are totally correct and i Did that.

The Columns that i created ( Month and Year ) was based on the data Column.

And i compared them just like I did in My formula:

Column = LOOKUPVALUE('Cost'[Cost],'Cost'[Year],Products[Year],'Cost'[Month],Products[Month],Prodcuts[Month],'Cost'[Product],products[Product])