Forum Discussion
Dax - Project approach
Hi, please try the Following:
You can try to do this in two ways,
- Create a Rank Column for associates based on the available slots per panel.
Use RANKX() to rank associates by panel, taking into account their eligibility.
Use FILTER() to select the available associates for each panel.
Apply logic to prevent duplicate mapping by filtering already mapped associates when assigning to the next panel.
- Using Power Query with DAX
->Load Table 1 and Table 2 into Power Query. Merge the Associate Table with them based on matching skills. Make sure you also apply the grade condition in the merge, so only associates with a grade greater than or equal to the required panel grade will be merged.
->If the ASS Skill in the Associate Table matches the panel skill in the Panel Table, we want to keep the row for further processing. Use the Grade Matching criteria where associates with grade a can only be mapped to panels with grade a or higher (e.g., sa), and so on. You can use conditional columns to evaluate this (if [ASS Grade] >= [panel grade] then "Eligible" else "Not Eligible"). Create a column that ensures both skills and grades are compatible (if [Skill Match] = "Yes" and [Grade Match] = "Yes" then "Eligible" else "Not Eligible").
-> In Power Query, group the merged data by Panel ID and Associate ID and then add a ranking column. Use an index column to ensure the ranking is based on an arbitrary order (you can rank based on Associate ID or any other sorting field). Now, the rank will automatically create a “first come, first served” scenario, where you only map associates if their rank is within the available slot for that panel.
-> Create a custom column that assigns an associate to a panel based on available slots. For each panel, if the associate's rank is within the available slots, they will be mapped. If not, they won’t be mapped (if [Rank] <= [Available Slot] then "Mapped" else "Not Mapped").
-> After ranking associates by panel, filter for the first occurrence (i.e., the first panel they are mapped to). Remove duplicates where an associate has been assigned to multiple panels.
-> Once the mapping is complete in Power Query, load the data into Power BI and visualize the final results. You’ll now have a table that shows each associate's mapped status and the panel they are assigned to, respecting the available slots and ensuring that no associate is mapped to more than one panel.
Please let me know if this works for you.
The problem here is after Ranking suppose A1,A2,A3,A4 can be mapped to P1 ( having 3 slots) and also to P2(also having 3 slots). Now based on Comparing Rank with slots , A1,A2,A3 will be mapped to P1 and P2. Based on first occurence A1,A2,A3 stays mapped to P1 .Now P2 has 3 empty slots . How to match A4 to P2? This become more complex when we more panels and associates..
Hi, let us try this in two phases:
- 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()
)
- 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:
Associate
Skill
Rank (Based on Matching Criteria)
Available Slots (P1)
Available Slots (P2)
a1
python
1
3
3
a2
python
2
3
3
a3
java
3
3
3
a4
python
4
3
3
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:
Associate
Skill
Rank
Mapped Status
Mapped To
a1
python
1
Yes
P1
a2
python
2
Yes
P1
a3
java
3
Yes
P1
a4
python
4
Yes
P2
Please try this and let me know.
