Dax - Project approach

[Faisalmbg143]

 Hi , I need to do mapping  between two tables

Table 1- Associate Table 

Ass Name	Ass ID	ASS Skill	ASS Grade
a1	123	python	a
a2	1234	python	a
a3	2345	java	a
a4	5679	python	a
a5	56789	java	a
a6	9875	python	a
a7	0987456	python	a
a8	432	excel	a
a9	123789	power bi	sa

Table 2 : Panel table

panel name	panel id	panel skill	panel grade	available slot
p1	12	python	sa	3
p2	23	sql	sa	3
p2	23	python	sa
p3	567	java	sa	1
p4	789	power bi	a	4

I need to get a chart or table somwhat like :

Associate	associate skill	mapped status	mapped to
a1	python	y	p1
a2	python	y	p1
a3	java	y	p3
a4	python	y	p1
a5	java	n
a6	python	y	p2
a7	python	y	p2
a8	excel	n
a9	power bi	n

Criteria - I need to map Associates with Panel based on Skill , Grade ( Associates can be mapped to Senior Associates and above, SA can be mapped to Manger and abve and so on ). I also need to make sure ,Associates are not mapped to more than one panel.Only  n number of associates should be mapped to one panel where n is available slot. Please note , one panel might have more than one skill ( in excel his skill is separated by comma).

Aproach - I tried multiple ways but none giving accurate result. I am first merging the associates based on skill. Then created one column to check grade condition. Then creating Associate rank to compare with avbailable slot. But the problem is one associate is getting mapped to more than one. I tried removing duplicate mapping based on panel ranking but with  this approach  many associates that can be mapped are not mapping because of no iterative option in dax. Please suggest how can i approach.

[HCA]

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:

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.

[Faisalmbg143]

didnt work. Can you do it in a sample pbix file for this ?

[HCA]

Hi, please try the Following:

You can try to do this in two ways,

1. 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.

1. 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.

[Faisalmbg143]

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..

[HCA]

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:

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.

[DataNinja777]

Hi @Faisalmbg143 ,

To achieve accurate mapping with your criteria, you can consider a systematic approach, using a combination of ranking, conditional mapping, and iterative assignment. Here’s a suggested process:

Approach

1. Filter Based on Skills and Grades: First, filter associates and panels based on matching skills and eligible grades. This will help reduce the dataset and make further processing more efficient.
2. Rank Associates for Each Panel: Create a rank column for associates based on each panel they are eligible for. The rank should respect the panel’s available slot so that only a limited number of associates can map to each panel.
3. Create Mapping Logic with Power Query (Suggested): Since DAX lacks iterative capabilities, Power Query might be more suitable for this logic. Power Query allows looping and transformation steps that can handle conditional assignments and ranks. You can:
• Use Power Query to group associates by skill and grade compatibility.
• Implement an assignment logic that checks available slots and maps associates one-by-one until slots are filled.
4. Conditional Assignment Column: In Power Query, add a column to indicate mapped status based on the available slot. As each panel’s slots are filled, additional associates of the same skill should not be mapped.
5. Finalize Mapping:
• For associates that could not be mapped due to slot limitations or skill mismatches, leave the mapped status as "n" and mapped to blank.
• For associates who meet criteria and fill available slots, set mapped status to "y" and populate mapped to with the respective panel name.

Steps in Power Query

1. Import Data: Load both tables into Power Query.
2. Merge Tables on Skills: Merge Associate Table and Panel Table on ASS Skill = panel skill with conditional filtering on grades.
3. Add Rank for Slot Limitation: Create a ranking based on the merged table, grouping by each panel id and sorting by an arbitrary rank (e.g., Ass ID) to select the top n associates.
4. Conditional Mapping Column:
• Use an if statement to assign mapped status = "y" if the rank is within the available slot for each panel.
• Otherwise, assign mapped status = "n".
5. Remove Duplicates: For associates eligible for multiple panels, select the highest-ranked match or implement custom logic based on business requirements to determine the most appropriate mapping.

This Power Query-based solution should meet your requirements by allowing a stepwise, rank-based mapping approach, ensuring that each associate only maps to one panel and honoring the available slots.

Best regards,

[Faisalmbg143]

I  have done almost the same thing. Problem is suppose A1 to A10 can be mapped to Panel 1 and Panel 2 . Now Available slot is 3 for both. So based on Rank , A1-A3 got mapped to Panel 1 and Panel 2. I able to remove duplicate mapping by ranking Panel. But here Panel 2  remain unmapped even when A4-A6 could be mapped to them.

Right now , I am ranking the associates based on associate ID grouped by Panel only when the match is possible.

Can you explain how to achieve this  where I can rank associates based on available slots , also making sure that after filling the slots, same associates do no map with the next panel even if they meet the criteria..

1. Rank Associates for Each Panel: Create a rank column for associates based on each panel they are eligible for. The rank should respect the panel’s available slot so that only a limited number of associates can map to each panel.