Dax - Project approach

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,