Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredJoin us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.
Hello PowerBI community
I have two tables PreviousOrders and FutureOrders.
PreviousOrder
Facility | Processing Time | OrderType |
A | 13 | Express |
A | 15 | Normal |
B | 19 | Express |
C | 10 | Normal |
A | 11 | Express |
B | 15 | Express |
B | 13 | Express |
C | 14 | Normal |
FutureOrders.
Facility | OrderType | ExpectedTime |
A | Express | =averageif(A and Express in First table) |
B | Express |
|
C | Express |
|
C | Normal |
|
A | Express |
|
A | Normal |
|
B | Express |
|
C | Normal |
|
I would like the ExpetedTime in the “FutureOrders” table to be the "Processing Time" for that facility and order type based on the data in PreviousOrder.
Is there an easy way to do this in DAX?
Thank you
Solved! Go to Solution.
Hi @bklyn3
you build a many to many relationship between the two tables based on the facity column. Then New column
AVERAGEX (
FLITER (
RELATEDTABLE ( PreviousOrder ),
PreviousOrder[OrderType] = FutureOrders[OrderType]
),
PreviousOrder[Processing]
)
Ì know this is solved but I would like propose a alternative without DAX in Power Query
Merge both tables
Expand using aggregate option --> sum (average not directly available)
Change List.Sum to List.Average in the formula.
Done, no many-2-many needed.
Thank you
Hi @bklyn3
you build a many to many relationship between the two tables based on the facity column. Then New column
AVERAGEX (
FLITER (
RELATEDTABLE ( PreviousOrder ),
PreviousOrder[OrderType] = FutureOrders[OrderType]
),
PreviousOrder[Processing]
)
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
6 | |
4 | |
3 | |
3 |
User | Count |
---|---|
13 | |
11 | |
8 | |
8 | |
8 |