Overriding a dimension value in a calculation - old article number to new article number

[Binkhorst]

I have a report that calculates the turnover per article. Some articles have been received a new article number but for reporting purposes I want to show the turnover combined with the old article. There is a dimension table with the relationship between the old and the new article number but I am trying to find the best way to use this in my calculations. 

Dimension table 1 : article 

Article number	Article name
1	Product 1
2	Product 2
3	Product 3

Dimension table 2: old article number new article number

Article number	New Article number
1	3

Sales table

Article number	Sales Amount
1	10
2	15
3	20
1	15
3	10

I need the Sales to show a turnover of 55 on article 3 and 0 on article 1. How do I override the existing relationship I have between the dimension table and the fact table. I would really appreciate the help.

[Binkhorst]

Thank you amitchandak, I tried your formula but I could not make this check : Dim2[Article number] = Sales[Article number] in my dax code so something seems to be missing? 

[PC2790 · Accepted solution]

This is how I would do it,

Creating a new column in both Sales and Article Table whihc would have the overridden article value.

I have used LOOKUPVALUE for the same

Then creating a relation ship between these two tables base don overridden article number

and then visualise it as normal. Refer attached sample file

The only downside to this is the many to many relationship that it forms that you can avoid using a bridge table.

[Binkhorst]

Thank you PC2790, your solution works. I was able to avoid the many to many relationship by using the function USERELATIONSHIP.

CAL Sales Amount = CALCULATE(

sum(SalesTable[Sales Amount] ),

USERELATIONSHIP(SalesTable[New Article Number],ArticleTable[Article number])

)

[amitchandak]

@Binkhorst , Create a new column in sales Table

New Ids =

var _1 = maxx(filter(Dim2, Dim2[Article number] = Sales[Article number]), Dim2[New Article number])

return

if(isblank(_1) , [Article number] , _1)

Join this with dimesion