Returns only first row with lookupvalue

Hi Laufer_Israel ,

You can create columns like DAX below.

Rank = CALCULATE(COUNT(Table1[Code]),FILTER(Table1, Table1[Code]<=EARLIER(Table1[Code])))
 
Total sales 2018_New=IF([Rank]=1, [Total sales 2018], BLANK())

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Laufer_Israel 

if you need to do this in Power Query, use this slightly changed query

let
	Source = #table
	(
		{"Code","Name","Product","Quantity","Total sales 2018"},
		{
			{"1","One","PP","2","220"},	{"1","One","SS","3","220"},	{"1","One","YY","4","220"},	{"1","One","MM","9","220"},	{"2","Two","PP","1","167"},	{"2","Two","SS","4","167"},	
			{"2","Two","YY","7","167"},	{"2","Two","MM","2","167"},	{"3","Three","PP","3","701"},	{"3","Three","SS","1","701"},	{"3","Three","YY","3","701"},	{"3","Three","MM","8","701"}

					}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Name", type text}, {"Product", type text}, {"Quantity", Int64.Type}, {"Total sales 2018", Int64.Type}}),
    Group = Table.Group(ChangeType, {"Code", "Name", "Total sales 2018"}, {{"AllRows", each _, type table [Code=number, Name=text, Product=text, Quantity=number, Total sales 2018=number]}}),
	AddIndex = Table.TransformColumns
	(
		Group,
		{{"AllRows", each Table.AddIndexColumn(_,"Index",1 )}}
	),
	AddColumn = Table.TransformColumns
	(
		AddIndex,
		{{"AllRows", each Table.AddColumn(_,"Total Sales 2018 new",(add)=> if add[Index]=1 then add[#"Total sales 2018"] else null )}}
	),
    DeleteOtherColumns = Table.SelectColumns(AddColumn,{"AllRows"}),
    ExpandAllRows = Table.ExpandTableColumn(DeleteOtherColumns, "AllRows", {"Code", "Name", "Product", "Quantity", "Total sales 2018", "Total Sales 2018 new"}, {"Code", "Name", "Product", "Quantity", "Total sales 2018", "Total Sales 2018 new"})
in
    ExpandAllRows

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi Laufer_Israel ,

Are you able to this kind of lookup? I yes. May I know please what DAX did you use?

Thank you 🙂