Dax - Project approach

Hi, let us try this in two phases:

1. Initial Assignment of Associates to Panels

In Power Query:

->Merge Tables: Merge the Associate Table and the Panel Table based on the skill and grade eligibility.

->Rank Associates within Panels: Add an Index column to rank associates within each panel based on the Associate ID (or another sorting field). This helps you prioritize who gets assigned first.

->Check Available Slots: For each panel, ensure that associates are only mapped to the panel if their rank is within the available slots for that panel.

In DAX:

->Create a Rank for each associate within each panel:

Rank = RANKX(

    FILTER(Table, Table[Panel ID] = EARLIER(Table[Panel ID])),

    Table[Associate ID],

    ,

    ASC

)

->Assign Associates to Panel:

AssignedToPanel =

IF(

    [Rank] <= [Available Slots],

    [Panel ID],

    BLANK()

)

1. Reassign Unassigned Associates to Remaining Slots

In Power Query:

->Create a Conditional Column to Track Unassigned Associates: Add a new column that flags associates who haven't been assigned to any panel yet. You can do this by checking if the Mapped To column is null or blank. Create a new step to filter the associates who are still unassigned.

->Reassign Unassigned Associates: After the first pass of assignments, filter the table to include only unassigned associates and check the available slots in all panels. You should then map the unassigned associates to panels that still have available slots. Use another Index Column to rank these unassigned associates. If an unassigned associate’s rank is less than or equal to the available slots of any panel, assign them to that panel.

In DAX:

->Create a new calculated column to flag associates that are not yet assigned to a panel: IsUnassigned = IF(ISBLANK([MappedToPanel]), 1, 0)

->Now, for the unassigned associates, you will need to use a logic that checks available slots across all panels that still have capacity and assign them appropriately. You can use CALCULATE and FILTER to check if the available slots in a panel can accommodate the unassigned associates:

ReassignedToPanel =

IF(

   [IsUnassigned] = 1 &&

   [Rank] <= [Available Slots] &&

   ISBLANK([MappedToPanel]),

   [Panel ID],

   BLANK()

)

Suppose we have these two panels (P1 and P2), and both have 3 available slots:

Initial Assignment: a1, a2, and a3 can be mapped to both P1 and P2 because of their skill and rank. Based on your ranking logic, you first assign a1, a2, and a3 to P1.

After the first pass, P1 has 3 slots filled and P2 still has 3 available slots, but a4 is still unassigned.

Reassign Unassigned Associates: Now, check the remaining unassigned associates (a4) and check the available slots in P2. Since P2 still has 3 available slots, you can assign a4 to P2.

Now, the table should look like this:

Please try this and let me know.