Combining two rows with same ID, but selecting only one

[sujesth]

Hi,

I'm having some trouble combining rows for products that have the same ID. What I am trying to do is have just one line item for a product that appears more than once in my table.

Current Table Sample

REF	YEAR	PRODUCT	COST	PERSON
A1	2023	Pears	$       1.50	Bob
B2	2023	Apples	$       1.30	Bob
B2	2023	Red Apples	$       1.30	Jane
	2023	Oranges	$       1.00	Jane
C26	2024	Green Lime	$       1.10	Bob
C26	2024	Lime	$       1.10	Lisa

- not all the items have a REF

- the product name may be a little different, but the REF and COST are really what I need to focus on. 

Desired Table Sample

REF	YEAR	PRODUCT	COST	PERSON
A1	2023	Pears	$       1.50	Bob
B2	2023	Red Apples	$       1.30	Jane
	2023	Oranges	$       1.00	Jane
C26	2024	Lime	$       1.10	Lisa

I'm not able to essentially "eliminate" a row. Calculated columns don't give me what I need and I've been unsuccessful with measures.  This is the latest measure I used, but this just repeats the cost value. Not sure what I'm doing wrong here. 

Product_Cost = VAR COST = ADDCOLUMNS('MyTable', "ActualCost", CALCULATE(MAX('MyTable'[Cost]), GROUPBY('MyTable','MyTable'[Ref]))) RETURN MAXX(COST, IF('MyTable'[Cost] = [ActualCost], 'MyTable'[Cost],0))

Any help will be very much appreciated! Thank you!

[sujesth]

Thank you @Ahmedx and @Arul! The solution using two measures suggested by @Ahmedx worked better for my purposes. Thank you!

[Ahmedx · Accepted solution]

you can not create a new table, but simply apply a conditional filter to your table using two measures
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhkAP-DuxcAVJ0sSp?e=OHmJ59

[Arul]

@sujesth ,

Try this as a calculated table,

Test Table = 
VAR _tempTable = 
SUMMARIZE(
    'Table',
    'Table'[REF],
    "Year",MAX('Table'[YEAR]),
    "Product",MAX('Table'[PRODUCT]),
    "Cost",MAX('Table'[COST]),
    "Name",MAX('Table'[PERSON])
    )
RETURN _tempTable

Thanks,

Arul