cancel
Showing results for
Did you mean:  Super User

## Calculated column with data from not related table

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?

1 ACCEPTED SOLUTION  Super User

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

2 REPLIES 2  Super User

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

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  