Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
joshua1990
Post Prodigy
Post Prodigy

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
wdx223_Daniel
Super User
Super User

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

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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

olgad
Super User
Super User

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

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.