Forum Discussion
Solving 'The Assignment Problem' with Lambda
In attached file PQ Assignment2 generates the required rows only
Though, it's slower than djclements initial approach (PQ Assignment1)
In attached file PQ Assignment2 generates the required rows only
Though, it's slower than djclements initial approach (PQ Assignment1)
Though the discussion starts by asking for a formula solution, this PQ solution is very elegant, so I've rung the bell on this one. Really can't go wrong with any of the solutions offered in this thread. Very impressive work!
Agree, Impressive work from everyone!!!
It may be a bit slower but it's easy to follow to step through it. This is quite clever!
DAX was not even on my list of potential solutions. This is impressive that it can be done but also the fact it's reads very easy.
Yesterday I believed there was room for another Lambda solution that could handle more than 3 tasks - maybe up to 10 or so. My thinking was to add a recursive element to the REDUCE portion of the formula:
Unrealized formula:
In seeing the various solutions being added it doesn't seem worth the effort. Also, Python can make quick work of the 'heavy lifting' portion of things in generating the permutations:
import itertools list(itertools.permutations([1, 2, 3, 4], 3))Patrick2788 OK so I am finally able to submit my solution. I didn't research the problem and other solutions so probably re-invented the wheel but:
I believe a valid solution must be some sequence of min for a given column followed by the min of another column until you reach all the columns (note removing the selected row each step). So given 3 columns if there were no duplicate min values that would be only 6 options.
So given that I have created 3 lambdas in the attached:
MinLocations : this is the 'master' lambda that will output the result
MinLocations_calcs: this will iteratively go through each possible column order finding all possible min solutions
DropLines: this is a useful utility to drop any specific rows or column from within a table/range
I have no idea of performance but submit that to others to find out 🙂
- This might be the most original formula solution posted because it looks like you went about generating the permutations a different way. You'll forgive me If I didn't respond sooner (I first opened the workbook after dinner the other day and knew this was one of the solutions which needed some time to study!). If I understand the solution correctly, MinLocations is doing most of the heavy lifting in identifying min rows for possible solutions. DropLines discards what's not needed. If I understand it correctly, even though there's a function to discard, not a lot of 'waste' is being generated with MinLocations. This is very different than a combination-based solution that generates tons of permutations and filters for what's needed. This solution may do well in a speed test.
DAX is not the tool for such tasks, however tried to repeat with it
DEFINE VAR WorkerA = SELECTCOLUMNS ( 'Table', "Worker A", 'Table'[Worker], "Cost A", 'Table'[A] ) VAR WorkerB = SELECTCOLUMNS ( 'Table', "Worker B", 'Table'[Worker], "Cost B", 'Table'[B] ) VAR WorkerC = SELECTCOLUMNS ( 'Table', "Worker C", 'Table'[Worker], "Cost C", 'Table'[C] ) VAR Combinations = CROSSJOIN ( WorkerA, WorkerB, WorkerC ) VAR minCost = MINX ( Combinations, [Cost A] + [Cost B] + [Cost C] ) VAR Assignment = FILTER ( Combinations, [Worker A] <> [Worker B] && [Worker A] <> [Worker C] && [Worker B] <> [Worker C] && [Cost A] + [Cost B] + [Cost C] = minCost ) EVALUATE AssignmentI'm not good at DAX but see exactly what you query does. Well done!
