Map Inventory ID to multiple sales order - CC/Measure (looping or sequential allocation)

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: 200

Sales 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 ✔