Merge Two Tables based on Qualification

[totoalbin]

Hello,

I have two tables.  Table 1 shows the School and the different Standards they have.  Table 2 shows the teachers and their qualifications based on the three Standards.  Table 3 shows the possible lists of teachers that can be accepted based on the three standards.  Example School A had 7 possible Teachers wherein it doesn't have any Standard yet therefore all teachers can apply in School A.   In School B had only Nadia since she's the only one who has passed Standard 1 and Standard 3.   How can I ahieve this list in PowerBI?

Table 1. Student

School	Standard1	Standard2	Standard3
A	0	0	0
B	1	0	1
C	0	1	0
D	1	1	1
E	1	1	0

 Table 2. Teacher

Teacher Name	Standard1	Standard2	Standard3
Gretchen	0	1	1
Nadia	1	1	1
Inca	0	1	0
Michelle	0	0	1
Melina	1	0	0
Gail	0	1	1
Tessa	1	1	0

Table 3. Qualified

School	Teacher
A	Gretchen
A	Nadia
A	Inca
A	Michelle
A	Melina
A	Gail
A	Tessa
B	Nadia
C	Gretchen
C	Nadia
C	Inca
D	Nadia
E	Tessa
E	Nadia

Thank you very much,

Alvin

[TomMartens · Accepted solution]

Hey,

first make the names of the Standards in both tables distinct, like S_S1, S_S2, and S_S3 for the Standards in the school table and T_S1 ... in the teachers table

Assuming that your tables are called School and Teacher ...

In PowerQuery (meaning no DAX) add a new custom column to the school table like so:

Basically this will create a cartesian product of both tables ...

Expand the table "Teacher"

Now create 3 custom columns called Q_S1, Q_S2_, and Q_S3 (Q means qualifies), here is the formula for Q_S1:

if [S_S1] = 0 then 1 else if [T_S1] = 1 then 1 else 0

This creates the value 1 if both columns (School and Teacher) have a value of 1 or the school value is 0, otherwise the formula returns 0. 

Adjust the column references for the columns Q_S2 and Q_S3 accordingly.

Create another custom column and call it "Qualified" or something similar and use this formula:

if [Q_S1] + [Q_S2] + [Q_S3] = 3 then "qualified" else "not qualified"

Now you have a "School" table that also contains the "Teacher" content and a column called "Qualified" that you can use to filter.

Hopefully this gives you an idea.

Regards,
Tom

[totoalbin]

Thank you very much it works!

[totoalbin]

Thank you very much havent tried this yet but I will once I get back.

[Greg_Deckler]

Can you post that data as something that can be copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[totoalbin]

I apologize for this.  Will correct it.

Thank you very much