Dax lookupvalue return top row

[Powerbiji99]

Please help with this Dax:

I have a purchase ref#=lookupvalue(‘table1’[ref#],
‘Table1[purchase amount],’table2[purchase amount]

Issue is table 1 has multiple line that repeat the purchase amount for a reference number. I want only the first row to be looked up and returned.

[Anonymous]

Hi @Powerbiji99 

Could you please share the dummy data set and expected output. 

Regards

Uphar

Happy To Help!

[Powerbiji99]

The goal is to link 2 datasets by a reference number. The  lookupvalue Dax is being used to match 4 variables and return reference number if match. Problem with the amount is that it's being double, triple counted depending on number of lines 

[Powerbiji99]

The data set in table 1 is an extract. There are multiple lines with the same amount referencing the same ref#.  The multiple lines are due to coding distribution. 

table 2 on the other had only has one line referencing the ref#

[Powerbiji99]

Also, I can't use data model to link the ref# because there are 3 other variables in the lookupvalue 

[AlB]

Hi @Powerbiji99 

What determines the first row? What column? A date perhaps? Internally DAX knows no order in the rows so you need something to specify that order. With that you could extract the MIN of the results that have that purchase amount.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers