Is it possible to solve this problem with DAX?

[sashaxiv]

Morning All!

I was wondering if it would be possible to solve with Dax a problem like this:

• I have a table of stores and their limits.
• I have a table of boxes to be assigned to the stores

Any ideas to define a column "GiveTo" that assigns boxes to stores? 

ID	Limit			Id	Units	GiveTo
Store 1	500			Box 1	100	Store 1
Store 2	400			Box 2	300	Store 1
Store 3	300			Box 3	200	Store2
				Box 4	200	Store2
				Box 5	100	Store 3

It would be easy to solve this  in Java/C...... ( While assigned<limit do sth...), but what about DAX?

Thanks in advance!

[Anonymous]

Hi @sashaxiv , 

I think your calculate logic contains looping. As far as I know, dax doesn't support looping currently.  Here I suggest to try to Power Query.

Here I suggest you to try List.Generate function.

For reference: List.Generate in Power Query: Tutorial with Easy Examples

Accumulative, conditional accumulative in Power Query

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

You'd need an index column or similar on both tables, to define the precedence of the stores and the order in which the boxes should be given out.

Then you could create a helper table like

Store limits = ADDCOLUMNS( SUMMARIZE( 'Table1', 'Table1'[Index], 'Table1'[Store]), "Upper bound",
var currentIndex = SELECTEDVALUE( 'Table1'[Index])
return CALCULATE( SUM('Table1'[Limit]), 'Table1'[Index] <= currentIndex)
)

and you could add a new column to the other table like

Give to store =
var currentIndex = 'Table2'[Index]
var totalUnits = CALCULATE( SUM('Table2'[Units]), 'Table2'[Index] <= currentIndex)
var summaryTable = TOPN(1, FILTER('Store limits', 'Store limits[Upper bound] >= totalUnits), 'Store limits'[Index], ASC)
return SELECTCOLUMNS( summaryTable, "@val", [Store])

[sashaxiv]

On the limit. For example, store 1 has a limit of 500. Boxes can be assigned to store1 if the sum of units of all assigned boxes is less than 500.

[Arul]

@sashaxiv ,

It is possible, based on what condition do you need to define the column?

Thanks,

Arul