Calculated column with data from not related table

[joshua1990]

Hi all!

Ich have 2 transactional tables und two dimensional tables

• Transactional 1: Sales per Article per Week
• Transactional 2: Orders per Article per Week
• Dimensional 1: Table of all Articles (unique)
• Dimensional 2: Calendar

Both transactional tables are linked (Many to One) to both dimensional tables.

Now I would like to add a calculated column into the first table with the number of orders from the second week respectively for the same article and same week.

How would you do that?

[wdx223_Daniel · Accepted solution]

NewColumn=VAR _week=RELATED(Calendar[Week]) VAR _article=RELATED(Dimensional1[Article]) RETURN COUNTROWS(FILTER(Transaction2,Transaction2[Week]=_week&&Transaction2[Article]=_article))

[olgad]

Hi there, 
what you could try, create a primary key on both tables using concatenate of article and date/week field

PrKey = Orders[ArticleID]&"-"&Orders[Date]
PrKey = Sales[ArticleID]&"-"&Orders[Date]

Then create a relationship between the Orders and Sales on that key ( it will be an inactive one)

Then on sales table, create a column:

Calculate(Sum(Orders[Order Quantity]), USERELATIONSHIP(Orders[PrKey], Sales[PrKey]))

If your relationship will be many to many between sales and orders, then you will get a sum of orders, if inittially aour tables are aggregates per week, then you have what you wanted.
Customer 1 Article 1 Week 1  500 Revenue  200 orders

Please let me know if that helped.
Customer 2 Article 1 Week 1  200 Revenue  200 orders