Forum Discussion
Map Inventory ID to multiple sales order - CC/Measure (looping or sequential allocation)
We have two tables: an Inventory table and a Sales Order table, both linked by Item ID.
When a sales order is placed, it is mapped to the closest available inventory for that item based on length and width constraints.
For example, for Item ID “ABC”, suppose the inventory has a length of 500 and a width of 200. If we receive 5 sales orders for item ABC, each requiring a width of 180 and a length of 100, then this single inventory record is sufficient to fulfill all 5 orders. In this case, the same inventory ID should be assigned to all 5 sales orders in a calculated column or measure.
Once that inventory record is fully consumed, it must not be assigned to any subsequent sales orders.
Conceptually, this resembles a looping or sequential allocation process:
for each sales order, select the best matching inventory, assign the inventory ID, reduce the remaining available quantity (length/width), and then carry forward the remaining balance for subsequent orders.
Is it possible to implement this kind of logic in Power BI?
Sample data below for 1 Item ( Inventory and sales order)
| Inventory ID | Item Id | Length | Width |
| PIO-001100 | BKFL578812 | 10000 | 200 |
| PIO-001101 | BKFL578812 | 100000 | 240 |
| PIO-001102 | BKFL578812 | 80000 | 600 |
| Sales Order | Sales Line no | Sales ITEM | Sales Length | Sales Width | Sales Quantity |
| LODEO-00235878 | 2 | BKFL578812 | 5868 | 90 | 3 |
| LODEO-00236882 | 2 | BKFL578812 | 2291 | 40 | 3 |
| LODEO-00236882 | 3 | BKFL578812 | 2470 | 40 | 3 |
| LODEO-00236882 | 4 | BKFL578812 | 2649 | 40 | 3 |
| LODEO-00236882 | 9 | BKFL578812 | 3544 | 40 | 3 |
| LODEO-00236882 | 8 | BKFL578812 | 3365 | 40 | 3 |
| LODEO-00236882 | 18 | BKFL578812 | 5154 | 40 | 3 |
| LODEO-00236882 | 17 | BKFL578812 | 4975 | 40 | 3 |
hi Vivek26
👉 This kind of looping / sequential allocation logic cannot be implemented correctly using DAX (measures or calculated columns).
Why this is not feasible in DAX
What you’re describing is a stateful, sequential process:
Take the first sales order
Find the best matching inventory record
Assign the Inventory ID
Reduce the remaining available Length / Width
Move to the next sales order and repeat using the updated balance
This requires:
Row-by-row iteration
Memory of previous allocations
Mutating remaining inventory quantities
DAX does not support this.
DAX is:Declarative (not procedural)
Stateless
Evaluated independently per row or filter context
Because of that:
Measures cannot “remember” prior rows
Calculated columns cannot loop across rows in order
You cannot decrement inventory progressively across sales lines
Any DAX solution would look correct only visually, but would break as soon as order, filters, or context change.
The correct way to solve this
✅ Option 1 — Power Query (Recommended inside Power BI)If the allocation logic can be done at refresh time, then:
Use Power Query (M)
Sort sales orders deterministically (e.g. by order date, line number)
For each Item ID:
Iterate through sales rows
Assign inventory
Track remaining Length / Width
Output the assigned Inventory ID as a real column
This is the most reliable option inside Power BI.
✅ Option 2 — Upstream processing (Best practice)
For production-grade scenarios:
Perform allocation in:
SQL
Python
Spark / Fabric notebooks
Store the result as:
A fact table
Or a bridge table (Sales Line → Inventory ID)
Then Power BI becomes purely analytical, which is exactly what it’s designed for.
❌ What not to do
Don’t try to force this with DAX measures
Don’t rely on ranking + running totals to “simulate” consumption
Don’t attempt circular logic between Inventory and Sales tables
Those approaches will not be stable or correct.
Conceptual illustration
Inventory (Initial)
PIO-001100 → Length: 10000, Width: 200Sales Orders (sequential)
Order 1 → consumes 100x180
Remaining inventory → 9900 x 20
Order 2 → consumes 100x180
Remaining inventory → 9800 x 20
...
Order N → until exhausted
Next order → move to next inventory ID
This kind of stateful mutation must happen before the data reaches the Power BI model.Summary
❌ Not possible in DAX (by design)
✅ Possible in Power Query (refresh-time logic)
✅ Best solved upstream (SQL / Fabric / Python)
✔ Power BI should consume the result, not compute it
If this explanation helped, please consider giving it a kudos 👍
And if it answers your question, feel free to mark it as the Accepted Answer ✔
4 Replies
hi Vivek26
👉 This kind of looping / sequential allocation logic cannot be implemented correctly using DAX (measures or calculated columns).
Why this is not feasible in DAX
What you’re describing is a stateful, sequential process:
Take the first sales order
Find the best matching inventory record
Assign the Inventory ID
Reduce the remaining available Length / Width
Move to the next sales order and repeat using the updated balance
This requires:
Row-by-row iteration
Memory of previous allocations
Mutating remaining inventory quantities
DAX does not support this.
DAX is:Declarative (not procedural)
Stateless
Evaluated independently per row or filter context
Because of that:
Measures cannot “remember” prior rows
Calculated columns cannot loop across rows in order
You cannot decrement inventory progressively across sales lines
Any DAX solution would look correct only visually, but would break as soon as order, filters, or context change.
The correct way to solve this
✅ Option 1 — Power Query (Recommended inside Power BI)If the allocation logic can be done at refresh time, then:
Use Power Query (M)
Sort sales orders deterministically (e.g. by order date, line number)
For each Item ID:
Iterate through sales rows
Assign inventory
Track remaining Length / Width
Output the assigned Inventory ID as a real column
This is the most reliable option inside Power BI.
✅ Option 2 — Upstream processing (Best practice)
For production-grade scenarios:
Perform allocation in:
SQL
Python
Spark / Fabric notebooks
Store the result as:
A fact table
Or a bridge table (Sales Line → Inventory ID)
Then Power BI becomes purely analytical, which is exactly what it’s designed for.
❌ What not to do
Don’t try to force this with DAX measures
Don’t rely on ranking + running totals to “simulate” consumption
Don’t attempt circular logic between Inventory and Sales tables
Those approaches will not be stable or correct.
Conceptual illustration
Inventory (Initial)
PIO-001100 → Length: 10000, Width: 200Sales Orders (sequential)
Order 1 → consumes 100x180
Remaining inventory → 9900 x 20
Order 2 → consumes 100x180
Remaining inventory → 9800 x 20
...
Order N → until exhausted
Next order → move to next inventory ID
This kind of stateful mutation must happen before the data reaches the Power BI model.Summary
❌ Not possible in DAX (by design)
✅ Possible in Power Query (refresh-time logic)
✅ Best solved upstream (SQL / Fabric / Python)
✔ Power BI should consume the result, not compute it
If this explanation helped, please consider giving it a kudos 👍
And if it answers your question, feel free to mark it as the Accepted Answer ✔Hi Vivek26
Option 1: Power Query (M) – Sequential Allocation
High-level logic in Power Query:
1.Filter Inventory by Item ID
2.Sort Inventory by priority (closest size / smallest fit)
3.Sort Sales Orders by order date / line no
4.For each sales order:
-Find first inventory where:
Inventory.Length >= Sales.Length * Quantity
Inventory.Width >= Sales.Width
-Assign Inventory ID
-Reduce Inventory.Length
-Carry remaining length forward
AllocateInventory = (SalesTable as table, InventoryTable as table) =>
let
SortedSales = Table.Sort(SalesTable, {{"Sales Line no", Order.Ascending}}),Allocation =
List.Generate(
() => [i = 0, Inventory = InventoryTable],
each [i] < Table.RowCount(SortedSales),
each [
Inventory = UpdateInventory([Inventory], SortedSales{i}),
i = [i] + 1
],
each AssignInventoryID([Inventory], SortedSales{i})
)
in
AllocationOption 2: Calculated Column (ONLY if assumptions hold)
This works only if:
-Inventory is never shared across items
-Orders are processed in a strict sequence
-Width is a hard filter
-Length is consumed linearlyStep 1: Create a cumulative demand per Item
Cumulative Length Required =
CALCULATE(
SUMX(
FILTER(
Sales,
Sales[Item ID] = EARLIER(Sales[Item ID]) &&
Sales[Sales Line no] <= EARLIER(Sales[Sales Line no])
),
Sales[Sales Length] * Sales[Sales Quantity]
)
)Step 2: Assign Inventory ID (first-fit logic)
Assigned Inventory ID =
VAR ReqLength = Sales[Cumulative Length Required]
RETURN
CALCULATE(
MIN(Inventory[Inventory ID]),
FILTER(
Inventory,
Inventory[Item Id] = Sales[Sales ITEM] &&
Inventory[Width] >= Sales[Sales Width] &&
Inventory[Length] >= ReqLength
)
)
Option 3: Measure → NOT POSSIBLE
Cannot update inventory
Cannot persist state
Recalculate every visual interactionSo looping allocation is mathematically impossible in measures.
Hi Vivek26 ,
I would take a moment to thank SavioFerraz , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
Hi Vivek26 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
