Forum Discussion
Post ProdigyCalculated 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?
NewColumn=VAR _week=RELATED(Calendar[Week]) VAR _article=RELATED(Dimensional1[Article]) RETURN COUNTROWS(FILTER(Transaction2,Transaction2[Week]=_week&&Transaction2[Article]=_article))
2 Replies
- olgad
Resident Rockstar
Hi there,
what you could try, create a primary key on both tables using concatenate of article and date/week fieldPrKey = 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 ordersPlease let me know if that helped.
Customer 2 Article 1 Week 1 200 Revenue 200 orders - wdx223_Daniel
Community Champion
NewColumn=VAR _week=RELATED(Calendar[Week]) VAR _article=RELATED(Dimensional1[Article]) RETURN COUNTROWS(FILTER(Transaction2,Transaction2[Week]=_week&&Transaction2[Article]=_article))
