Join 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!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
I have a table of Sales Order Details which has a Sales Order Number Column, Item Code Column and Quantity Ordered column. Let us call this SalesOrders
OrderNo | ItemCode | QtyOrd |
S1 | A1 | 10 |
S1 | B1 | 15 |
S1 | A2 | 12 |
S2 | A1 | 20 |
S2 | A2 | 11 |
S3 | B1 | 14 |
I have another table which has columns of Item Code, Location Code and Quantity. This table has details of in which location a particular item is stored and in which quantity. Lets call this Inventory Table.
Location | ItemCode | QtyStr |
L1 | A1 | 5 |
L1 | A2 | 3 |
L1 | B1 | 15 |
L2 | A1 | 7 |
L2 | A2 | 8 |
L2 | B1 | 10 |
L3 | A1 | 2 |
L3 | A2 | 4 |
L3 | B1 | 5 |
Now I want calculate --> For each sales order, for each item in that order, how many locations are required to fulfill that item requirement in that order. The locations with highest quantity should be checked first. So want to have a calculated column in SalesOrder where each row has the number of rows from Inventory to sum up the QtyOrd value(starting from the location with highest quantity).
Also, I want another column with location names which will fulfll that item order.
I hope I have explained my question clearly.
Kindly help @amitchandak @Anonymous @Jihwan_Kim @daxer-almighty @PaulOlding @Fowmy
@manuvats and @learner03 In case this got lost in all the noise, I made some improved versions of the stuff posted here. Both independent sales orders and 2 versions of FIFO. Order Fulfillment - Microsoft Power BI Community
Here's a file with a full solution that can be adjusted to your heart's content. It should be faster than @Greg_Deckler's formulas and is much more customizable. Not to mention it's infinitely easier to understand 🙂 without any flimsy string manipulation. Just download and watch the beauty of the solution 🙂
https://1drv.ms/u/s!ApyQEauTSLtOgZlOIZRShgVXkPOZnw?e=pVzdFb
Enjoy!
@daxer-almighty @Greg_Deckler I saw the solutions but I want to ask a question that, for example- for order S1 B1. if the 15 qty was fulfilled from Location L1 that had 15 ,then for S3, why does it again say L1 as it won't have any stock in the location. I think it should have L2 and L3. can you please look into this or clarrify?
@learner03 The original solution was for independent sales orders (in other words, ignoring FIFO). It assumes immediate or near immediate replenishment of stock. It's why I asked the question in my first response because while it makes the problem easier it isn't exactly realistic!! But, the original questioner specified independent calculations so I went with that initially!
@daxer-almighty Well, you used a calculated table even though you hid it, it is still there. Not sure how you would prove performance or fragility one way or another. But, it's essentially using exactly the same technique as mine using GENERATESERIES, CONCATENATEX, etc., etc. Fundamentally it is no different except you incorporate your table in the model versus mine is in a VAR. Which, if we were to get into a debate about which one took up more space in the model...
Yes. I did use a calc table but I don't have any remorse 🙂 Such a table should be, of course, created in PQ or in the source (you know the story). But I didn't have time (was too tired, it was deep into the night) to move this logic into PQ. But it's totally doable in there and is the right thing to do.
Second, DAX that has to operate on strings and does a lot of mangling-jumbling on them is fragile by definition. It's really for a reason that RMDB's store data in atomic portions, or at least they should if the person who builds them has any idea about a well-designed system. Here you've got the same story. After all, a tabular cube IS a database, albeit tabular (which does not change a single bit).
Third, where the heck did you see GENERATESERIES in my solution? 🙂 My code has no mention of it. On top of that, my code is infnitely more readable than yours, which also means it's more maintainable, customizable and user-friendly. This, of course, counts just as it counts in Python. And productivity counts, too. Just as it does in Python. It is for a reason that Python with its "strange" and strict formatting rules has become the most widely used language in the world in just too many fields to mention.
Fourth, I'm not sure about the space requirements. Maybe it does play a role, maybe it does not. But as long I don't have to save on storage/RAM, I always choose a model that is easier to maintain, understand and more flexible. It's exactly the same as it is for any programming task: First, make it correct, then make it fast, then (maybe) think about space... But time comes first these days and you know it. Also the ease with which people can grasp the code and make changes to it is of paramount importance. One should never lose readability/clarity of code from sight. Never. This is actually what distinguishes an excellent programmer from a programmer. "Any fool can write code that a machine can understand. It takes a good programmer to write code that people can understand." Rings a bell? 🙂 It should.
If the person who got the solution says later that it takes too much space in RAM (highly improbable but possible), well, then I'll try to think about a different solution. You also know the saying: "Premature optimization is the root of all evil." (quote by Sir Tony Hoare, popularized by Donald Knuth).
One last thing... It generally is much better (and advisable) to sacrifice memory for faster, more maintainable, more readable code.
I took a look at this UGLINESS:
VAR __SearchText = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],",",[QtyStr],DESC)
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
and I knew immediately it was not the correct approach. The code above is plain ugly. This is the first thing that comes to my mind when I (try to) read it and understand.
@daxer-almighty You missed the boundary case where there is not enough inventory to fulfill the order, your solution returns blank versus the correct answer of all locations.
@Greg_Deckler wrote:@daxer-almighty You missed the boundary case where there is not enough inventory to fulfill the order, your solution returns blank versus the correct answer of all locations.
@Greg_Deckler, are you 100% sure? First, it would be highly inconsistent to do that since the number of locations would be 0 and the string would contain all of them... Not the greatest of designs, is it?
But, I've checked the original post:
"Now I want calculate --> For each sales order, for each item in that order, how many locations are required to fulfill that item requirement in that order. The locations with highest quantity should be checked first. So want to have a calculated column in SalesOrder where each row has the number of rows from Inventory to sum up the QtyOrd value(starting from the location with highest quantity).
Also, I want another column with location names which will fulfll that item order.
I hope I have explained my question clearly."
Where does it imply what you claim?
It says "How many locations are required to fulfill that item..." Returning 0 means "there is no number of locations that would". And since there aren't any, it just stands to reason that the string be empty/BLANK.
@daxer-almighty If you checked my solution, my solution returns 3 for the location count and all the locations.
Please... mate. I don't exactly follow.
You're accusing my code of not adhering to what your code does? 🙂 Where did you get the rule from that stipulates that the lines which can't be fulfilled by any number of locations must be paired with all locations and the number must be 3 (the number of all eligible locations)? For the time being I can see that it's a rule you've invented yourself for some strange reason.
This rule of yours has 2 issues:
1. It makes the design inconsistent. How, without making calculations, can you distinguish between lines that CAN be fulfilled by all locations only and the ones that can't be fulfilled by any number of locations?
2. This rule does not exist in the original specification.
@daxer-almighty Granted, it's a business decision and could be open to interpretation given the original ground rules, all locations would be required to fulfill that order. Sure, additional inventory would be required but, sticking to the letter of the law and not making assumptions, the original ground rules would be that all locations should be returned.
@Greg_Deckler wrote:@daxer-almighty Granted, it's a business decision and could be open to interpretation given the original ground rules, all locations would be required to fulfill that order. Sure, additional inventory would be required but, sticking to the letter of the law and not making assumptions, the original ground rules would be that all locations should be returned.
It would be a very stupid business decision to return all locations and their number for lines that can't be fulfilled by any number of locations... Honestly, I wouldn't like to work for such a business 🤣
@manuvats Each each row in SalesOrder independent of the other rows or are they dependent? In other words, if for the S1 A1 10 in your first table, I calculate thate I need L1 and L2, can I no longer use the L1 for A1 items like for the S2 A1 20 line?
That is a good question and I haven't thought of that. I will ask the concerned team. For the time being, please treat fulfillment of orders independent of each other. The items in a particular location are replenished quickly after fulfillment of an order.
@manuvats OK, it is a bit messy and I will explain what is going on. Column is for the count and Column1 is for the list of locations. Now, DAX isn't great at sort orders, in fact by and large sort order is not guaranteed when dealing with DAX. We really can't use EVALUATE and ORDER BY in Power BI Desktop. However, CONCATENATEX does provide us something that can guarantee sort order. So, basically, I create SearchText that concatenates our locations together in the correct sort order by qty. Now, since I used CONCATENATEX, I need to convert things back into a table of values. That's the text to table pattern and essentially that is encapsulated in the calculation of __Table. So you can kind of ignore that because that's what that is doing. Now, for the Table1 variable, I am taking my converted list of Locations in a table in the correct sort order and I first add a running total so that I know how much inventory I have at that location including the inventory at locations with more inventory than the current location. I then add a column to decrement the sales order amount. Next, __Index finds the minimum index of the location where the decrement is still greater than or equal to zero. This is the first location where the running total meets the demand. After that it is straight-forward although you do have to account for the condition when the inventory does not meet the order amount.
Column =
VAR __ItemCode = [ItemCode]
VAR __QtyOrd = [QtyOrd]
VAR __Separator = ","
VAR __SearchText = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],",",[QtyStr],DESC)
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
ADDCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr])
)
VAR __Table1 =
ADDCOLUMNS(
ADDCOLUMNS(
__Table,
"__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr])
),
"__Decrement",[__RunningTotal] - __QtyOrd)
VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value])
VAR __Locations = COUNTROWS(FILTER(__Table1,[Value] <= __Index))
RETURN
IF(ISBLANK(__Locations),COUNTROWS(DISTINCT('Inventory'[Location])),__Locations)
Column 2 =
VAR __ItemCode = [ItemCode]
VAR __QtyOrd = [QtyOrd]
VAR __Separator = ","
VAR __SearchText = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],",",[QtyStr],DESC)
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
ADDCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr])
)
VAR __Table1 =
ADDCOLUMNS(
ADDCOLUMNS(
__Table,
"__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr])
),
"__Decrement",[__RunningTotal] - __QtyOrd)
VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value])
VAR __Locations = FILTER(__Table1,[Value] <= __Index)
VAR __Text1 = CONCATENATEX(__Table1,[__Word],",",[__QtyStr],DESC)
VAR __Text = CONCATENATEX(__Locations,[__Word],",",[__QtyStr],DESC)
RETURN
IF(ISBLANK(__Text),__Text1,__Text)
@Greg_Deckler Hi Greg, how to modify above if we take the case that the items are not replenished quickly and use the fulfilment criteria in which the sales order arrive (S1 first andthen S2...so on ascending order)in the warehouse? example- S1 is using stock of A1 from L1 and L2 and only 2 is left in L1
@learner03 Yeah, I had a feeling that question was coming at some point and have been going back and forth on whether it starts to fall down the DAX recursion rabbit hole. Let me take a look.
Well, Greg, it looks like PQ enters the stage 🤣🤣🤣
@daxer-almighty Incorrect yet again I am afraid. No need for PQ. @learner03 the following lists the number of locations taking into account a FIFO procedure for the sales orders. It is a minor tweak of the original solution. In this case, if you run out of locations capable of fulfilling the orders then it returns 0 locations capable of fullfilling the complete order. Could be tweaked for partial order fulfillment.
Column 3 =
VAR __ItemCode = [ItemCode]
VAR __Order = [Index]
VAR __QtyOrd = SUMX(FILTER('SalesOrders',[Index]<=__Order && [ItemCode]=__ItemCode),[QtyOrd])
VAR __Separator = ","
VAR __SearchText = CONCATENATEX(FILTER('Inventory','Inventory'[ItemCode] = __ItemCode),[Location],",",[QtyStr],DESC)
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
ADDCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__QtyStr",MAXX(FILTER('Inventory',[Location] = [__Word] && [ItemCode] = __ItemCode),[QtyStr])
)
VAR __Table1 =
ADDCOLUMNS(
ADDCOLUMNS(
__Table,
"__RunningTotal",SUMX(FILTER(__Table,[Value]<=EARLIER([Value])),[__QtyStr])
),
"__Decrement",[__RunningTotal] - __QtyOrd)
VAR __Index = MINX(FILTER(__Table1,[__Decrement]>=0),[Value])
VAR __Locations = COUNTROWS(FILTER(__Table1,[Value] <= __Index))
RETURN
__Locations+0
@Greg_Deckler, @learner03, @manuvats
Guys, I would really like to know if the code that @Greg_Deckler has given you will ever return on a real model. My suspicion is that it'll last for quite a time on a decent model. Might not even return. Seen such things in the past with calculated columns on big fact tables. Have even troubleshot such isuses here on this forum.
Please run this code on a real model with a SalesOrder table that has hundreds of millions of rows or whatever you've got (but make it real). Let's see whether I'm right or wrong.
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 |
---|---|
13 | |
11 | |
9 | |
8 | |
8 |