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 PanelEmployee IDNameAreaSub AreaLevel0201JohnSalesRegion1FL0342MarieOperationsPlant02Level025278LindsayOperationsPlant02Level018742RubySalesRegion2FL6453MatthewSalesRegion1Level05 And it goes on with all company's employees. Training PanelTraining IDNameLevelAreaSubareaTR001Negotiation TrainingFLSalesRegion1TR001Negotiation TrainingFLSalesRegion2TR032CommunicationTrainingLevel01OperationsPlant1TR032CommunicationTrainingLevel01OperationsPlant2TR032CommunicationTrainingLevel01OperationsPlant3TR032CommunicationTrainingLevel02OperationsPlant1TR032CommunicationTrainingLevel02OperationsPlant2TR032CommunicationTrainingLevel05SalesRegion1TR059Security TraningFLSalesRegion1TR239HR TrainingFLOperationsPlant1TR239HR TrainingLevel01OperationsPlant2TR037Power BI TrainingLevel05SalesRegion1TR050Manufacturing TrainingLevel02OperationsPlant02 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 DatabaseEmployee IDNameTraining NameTraining ID0201JohnNegotiation TrainingTR0010342MariePower BI TrainingTR0375278LindsayHR TrainingTR239 I have to create a new table, that looks like this: Employee IDNameAreaSub AreaLevelTraining RequiredTraining NameCompleted?0201JohnSalesRegion1FLTR001Negotiation TrainingYes0201JohnSalesRegion1FLTR059Security TraningNo0342MarieOperationsPlant02Level02TR032CommunicationTrainingYes5278LindsayOperationsPlant02Level01TR032CommunicationTrainingNo5278LindsayOperationsPlant02Level01TR239HR TrainingYes8742RubySalesRegion2FLTR001Negotiation TrainingNo6453MatthewSalesRegion1Level05TR037Power BI TrainingNo I tried a lot of dax stuff, but wasn't able to get done. A little help anyone? Regards!

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

Regular Visitor

4 years ago

Solved

Create a new table based on many conditions

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
4 years ago
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
many conditions.pbix41 KB

Forum Discussion

Create a new table based on many conditions

3 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Create table from another table with conditions

Create Table with Condition