LookUpValue Function -- Error

[Ciria]

Dears:

I have a table, named "Receptions", where material receptions information from multiples supplier and items are stored.

For trazeability issues, every supplier batch needs a internal batch. Of course we try to limit the amount of batches, but sometimes it happens.

In the below (and simple) example you can see the Product 1 has been received last time on July 06th, but two batches were received from Supplier 1.

Receptions Table	(Fact Table)
Product	Date	Price	Batch	Supplier batch	Supplier	Quantity
Product 1	06/07/2016	2,45	Lot-001	Batch 2016-1	Supplier 1	250
Product 1	06/07/2016	2,47	Lot-002	Batch 2016-2	Supplier 1	250
Product 1	05/04/2016	2,48	Lot-003	Batch 2016-3	Supplier 1	500

This is how the table I have created looks like in PowerBI.

Receptions Summary Table

Supplier Name	Full Name	Total Purchases (%) Family	Last Purchase	Last Purchase Price	Total Amount	Average of Unit Price € (Exchange Insurance)
Supplier 1	Product 1	81,59%	06/07/2016 0:00		18165	2,52
Supplier 1	Product 2	28,30%	21/06/2016 0:00		16694	10,28
Supplier 1	Product 3	99,69%	23/05/2016 0:00		16000	1,81
Supplier 1	Product 4	54,86%	16/03/2016 0:00		15070	1,23
Supplier 1	Product 5	87,19%	19/07/2016 0:00		12965	2,96
Supplier 1	Product 6	25,92%	06/07/2016 0:00		10253	4,6
Supplier 1	Product 7	16,21%	20/07/2016 0:00		9959	3,55
Supplier 1	Product 8	11,30%	04/02/2014 0:00		7315	1,8
Supplier 1	Product 9	33,75%	21/01/2016 0:00		7000	3,72
Supplier 1	Product 10	100,00%	19/07/2016 0:00		5850	2,51
Supplier 1	Product 11	72,16%	13/05/2016 0:00		5382	4,1

By using MAXA function I can find the Last purchase date.

What I can't is "the last price" for that date. In this case 2,47.

I was investigating about Lookupvalue but this functions seems to have problems when two values are found (my case, two receptions at 06 th of July with different price).

"if several rows in your table match your criteria, then VLOOKUP in Excel will only return the first (or last) match. On the contrary, LOOKUPVALUE in DAX may return an error if several rows match your criteria. This will occur if [OutputColumn] does not contain the same value for all matching rows".

Source: https://thedataspecialist.wordpress.com/2013/02/16/equivalent-of-vlookup-in-daxpart-i/

Please I need your help to find the right expression to give me the "maximum value of price" for the "last purchase date".

Regards,

[Ciria]

Dears

Just after clicking "post" an idea came up

I have used this, and it seems it works:

CALCULATE(MAXA(Receptions[Unit Price €)]);LASTDATE(Receptions[Reception Date]))

Regards,

[Ciria]

Dears

It seems the solution I found by myself works when you look for a value, this is the "unit price", "last purchase date", "amount", etc.

But, what about if want to look for a text value like "Supplier Name"?

I cannot use the above formula, it doesn't work

Any support?

Thanks in advance,

[Ciria]

Dears:

Any help??

Regards,

[Anonymous]

Hi @Ciria,

You can use below measure to get the supplier name:

lookupSupplierName = lookupvalue('Receptions Summary Table'[Supplier Name],'Receptions Summary Table'[Last Purchase],LASTDATE(VALUES('Receptions Summary Table'[Last Purchase])))

 

Regards,
Xiaoxin Sheng

[Anonymous]

@Ciria Please try below to get the table : 

MAx_Price = CALCULATE(MAXA(Maximum_Price[Price]),LASTDATE(Maximum_Price[Date]))

Measure_1 = LOOKUPVALUE(Maximum_Price[Supplier],Maximum_Price[Price],MAXA(Maximum_Price[Price]))

Regards