Create a new table based on many conditions

[pjotaramos01]

I'm creating a dashboard to show the training conclusion percentage filtering by all company's areas, sub areas and levels.

In order to get all the info, I have to check all trainings an employee is eligible. For this I get three excel sheets as databases:

HR Panel, that contains all personal unique info from employees such as the employee ID, name, area, sub area, level, etc.

Training Panel, that contains all training prerequisites infos (example: a X Training is only applied to Frontline Workers from Sales team). 

Completed Training Database, that contains all the employees that completed an specific training.

To get a visual look:

HR Panel

Employee ID	Name	Area	Sub Area	Level
0201	John	Sales	Region1	FL
0342	Marie	Operations	Plant02	Level02
5278	Lindsay	Operations	Plant02	Level01
8742	Ruby	Sales	Region2	FL
6453	Matthew	Sales	Region1	Level05

And it goes on with all company's employees.

Training Panel

Training ID	Name	Level	Area	Subarea
TR001	Negotiation Training	FL	Sales	Region1
TR001	Negotiation Training	FL	Sales	Region2
TR032	CommunicationTraining	Level01	Operations	Plant1
TR032	CommunicationTraining	Level01	Operations	Plant2
TR032	CommunicationTraining	Level01	Operations	Plant3
TR032	CommunicationTraining	Level02	Operations	Plant1
TR032	CommunicationTraining	Level02	Operations	Plant2
TR032	CommunicationTraining	Level05	Sales	Region1
TR059	Security Traning	FL	Sales	Region1
TR239	HR Training	FL	Operations	Plant1
TR239	HR Training	Level01	Operations	Plant2
TR037	Power BI Training	Level05	Sales	Region1
TR050	Manufacturing Training	Level02	Operations	Plant02

and it goes on with all required levels, areas and sub areas.

I also have a database that cointains all employees that concluded a training:

Training Database

Employee ID	Name	Training Name	Training ID
0201	John	Negotiation Training	TR001
0342	Marie	Power BI Training	TR037
5278	Lindsay	HR Training	TR239

I have to create a new table, that looks like this:

Employee ID	Name	Area	Sub Area	Level	Training Required	Training Name	Completed?
0201	John	Sales	Region1	FL	TR001	Negotiation Training	Yes
0201	John	Sales	Region1	FL	TR059	Security Traning	No
0342	Marie	Operations	Plant02	Level02	TR032	CommunicationTraining	Yes
5278	Lindsay	Operations	Plant02	Level01	TR032	CommunicationTraining	No
5278	Lindsay	Operations	Plant02	Level01	TR239	HR Training	Yes
8742	Ruby	Sales	Region2	FL	TR001	Negotiation Training	No
6453	Matthew	Sales	Region1	Level05	TR037	Power BI Training	No

I tried a lot of dax stuff, but wasn't able to get done.

A little help anyone?

Regards!

[ryan_mayu · Accepted solution]

@pjotaramos01 

can we do this in pq? i think that will be easier.

However the output is different from yours.

i got Matthew for Communication Training

and Maria has not completed for Communication training since we only see PowerBI training in training database

pls see the attachment below

[pjotaramos01]

Oh! It's exactly this! 

How did you do it? You merged the tables and created a custom collumn? How did you make the merged table? 

Thanks in advance!

[ryan_mayu]

I did that in PQ. you can check the transform steps in PQ.