Match multiple entries in one table with multiple entries in another table

[Alex_Heinze]

In my left-hand table (actually a view) with jobs that require 0..n skills (typically 1-5). In the right-hand table I've got technicians with their associated skills (typically 20-30). To qualify for a job, the technician needs to possess ALL skills.

I tried building a large view with several joins in which I get all techs for all required skills, but that view also includes technician B, because he has skill 1 for job 1 and skill 4 for job 2. I struggle to filter the candidates with all skills.

Appreciate your help.

Alex

[lbendlin · Accepted solution]

Here is the "correct"  solution. I'm sure this can be optimized.

Available Technicians = 
var j = '1 - Job Skills'[Job]
var s = SELECTCOLUMNS(Filter('1 - Job Skills','1 - Job Skills'[Job]=j),"Required",'1 - Job Skills'[Required skills])
var a = ADDCOLUMNS(VALUES('2 - Technician Skills'[Technician]),"Available",
        var te='2 - Technician Skills'[Technician] 
        var av=SELECTCOLUMNS(Filter('2 - Technician Skills','2 - Technician Skills'[Technician]=te),"avl",'2 - Technician Skills'[Technician Skills])
        return countrows(INTERSECT(av,s)))
return CONCATENATEX(filter(a,[Available]=countrows(s)),'2 - Technician Skills'[Technician],",")

See attached.

[Alex_Heinze]

Hi @lbendlin that's just awesome, thanks so much for taking the time. One last question: Which statement would I need to alter to get 1 line for each combination of job and technician (instead of the comma-separated list)? This would allow me to join additional details from the technician record.

[lbendlin]

You can add a step to split "Available Technicians" by delimiter into rows.

[lbendlin]

It's not easy to use views or joins for that.  You can use DAX measures based on INTERSECT().

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[Alex_Heinze]

Hi @lbendlin, thanks for the answer. Have uploaded my sample files to https://1drv.ms/f/s!AkgTNHqYeuPdg4lBeLhKGzjygnm7oQ , appreciate your help. 

[lbendlin]

While I prepare the "proper"  solution here's a quick and dirty workaround

Calculated table:

T = var f= FILTER(CROSSJOIN(SELECTCOLUMNS('1 - Job Skills',"Job",'1 - Job Skills'[Job],"Required",'1 - Job Skills'[Required skills]) ,SELECTCOLUMNS('2 - Technician Skills',"Technician",'2 - Technician Skills'[Technician],"Available",'2 - Technician Skills'[Technician Skills]) ),[Required]=[Available]) Return ADDCOLUMNS(f,"ReqSkill",CALCULATE(DISTINCTCOUNT('1 - Job Skills'[Required skills]),ALLEXCEPT('1 - Job Skills','1 - Job Skills'[Job])))

Add a calculated column to it

AvailSkills = CALCULATE(DISTINCTCOUNT(T[Available]),ALLEXCEPT(T,T[Job],T[Technician]))