topic Re: Is it possible to solve this problem with DAX? in DAX Commands and Tips

Is it possible to solve this problem with DAX?

sashaxiv — Mon, 09 May 2022 14:33:02 GMT

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!

Re: Is it possible to solve this problem with DAX?

Arul — Mon, 09 May 2022 14:39:40 GMT

sashaxiv ,

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

Thanks,

Arul

Re: Is it possible to solve this problem with DAX?

sashaxiv — Mon, 09 May 2022 14:44:21 GMT

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.

Re: Is it possible to solve this problem with DAX?

johnt75 — Mon, 09 May 2022 14:55:33 GMT

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])

Re: Is it possible to solve this problem with DAX?

Anonymous — Wed, 18 May 2022 08:33:14 GMT

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.