Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |