Get Values from table1 for corresponding values from table2 with duplicates

[Meru]

I have 2 tables, table1 with ID and table2 with corresponding old ID. I want a calculation where  ID from table1 and correspoding OLD ID in table2 need to consider and then need values(quantity, price, year) in table1 for calculation for all these matching IDs. For example consider ID1 (which has Old IDs as 2 and 4) so how can i filter table 1 for all these IDs and calucltae balue as per below screenshot. There are duplicates in OLD ID column as well (means there could be multiple OLD IDs against one ID).

[Meru]

@v-kelly-msft Thanks for your help but as I mentioned there are duplicates in Table2 (Old ID) column as well. Consider a new row with values (7, 6) in table 2 and a new row with values (7, 2020, 21, 0.15) in table1.  Now in table1, for ID 5 & 6 need to consider ID 3 but for ID 7 need to consider 6.

[v-kelly-msft]

Hi  @Meru ,

Could you pls provide a complete sample data with expected output for test?Better with your logic.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Meru]

@v-kelly-msft Consider below data set and calculations. Consider the changes highlighted in yellow. Also in the calculation for 2019, i need to multiply price of 2019 with qty of 2020. pls see calculation highlighted in red for better uderstanding.

[v-kelly-msft · Accepted solution]

Hi  @Meru ,

Create 2 columns as below:

_count = CALCULATE(COUNTROWS('Table1'),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))

_sum = 
var _qty=CALCULATE(MAX('Table1'[ Quantity]),FILTER('Table1','Table1'[Year]=2020&&'Table1'[ID]=EARLIER('Table1'[ID])))
var _price=CALCULATE(MAX('Table1'[Price]),FILTER('Table1','Table1'[Year]=2019&&'Table1'[ID]=EARLIER(Table1[ID])))
Return
IF('Table1'[_count]=1,_qty*_price)

And modify measure 2019 as below:

Value(2019) = 
var _sum=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2020&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[ Quantity])
var _price=SUMX(FILTER(ALL(Table1),'Table1'[Year]=2019&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[Price])
var _aver=DIVIDE(_price,MAX('Table1'[_count]))
var _newID=CALCULATETABLE(VALUES('Table1'[new ID]),'Table1'[_count]>1)
Return
IF(MAX('Table1'[new ID]) in _newID,_sum*_aver,SUMX(SUMMARIZE('Table1','Table1'[ID],'Table1'[new ID],"price",MAX('Table1'[_sum])),[price]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi @Meru ,

First create a column in table 1 as below:

new ID = 
var _lookup=LOOKUPVALUE('Table2'[ID],'Table2'[Old ID],'Table1'[ID],blank())
Return
IF(_lookup=BLANK(),'Table1'[ID],_lookup)

Then create 2 measures as below:

Value(2019) = SUMX(FILTER(ALL(Table1),'Table1'[Year]=2019&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[ Quantity]*'Table1'[Price])

Value(2020) = SUMX(FILTER(ALL(Table1),'Table1'[Year]=2020&&'Table1'[new ID]=MAX('Table1'[new ID])),'Table1'[ Quantity]*'Table1'[Price])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!