Conditional multiple values

[pjotaramos01]

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

We got a excel sheet, "Training DataBase", that shows all the employees that completed any training. This table contains only the employee ID, name, training name and the training ID.

In order to get all the others infos, I have 2 more 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). 

To get a visual look:

Training Database

Employee ID	Name	Training Name	Training ID
0201	John	Negotiation Training	TR001
0342	Marie	CommunicationTraining	TR032
5278	Lindsay	HR Training	TR239

HR Panel

Employee ID	Name	Area	Sub Area	Level
0201	John	Sales	Region 1	FL
0342	Marie	Operations	Plant 02	Level05
5278	Lindsay	Operations	Corporate	Level09

Training Panel

Training ID	Name	Level	Area	Subarea
TR001	Negotiation Training	FL	Sales	Region1
TR001	Negotiation Training	FL	Sales	Region2
TR001	Negotiation Training	FL	Sales	Region3
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	Level02	Operations	Plant3

The idea is to have a dashboard with area, sub area and level filters to show the percentage of conclusion based on each selection. 

I created a table that cross HR Panel and Database Training, so I was already able to create measures that count the total of areas/subareas/levels employees ID distinct values from HR Panel and Database Training, in order to show a percentage of conclusion from all this filters.

Table showed (filtered by Operations)

Employee ID	Name	Area	Training Name	Status
0342	Marie	Operations	CommunicationTraining	Completed
5278	Lindsay	Operations	HR Training	Not completed

But doing this I'm not able to get the real percentage because not everybody from an area is eligible to every training. I need to add a condition of area, sub area and level to the "Count HR Panel Eligible Employee" measure (currently is only a DistinctCount values from the HR Panel table) based on which training.

I'm struggling with getting this solution because I have many rows for each training.

Can anybody help me with this? 

Thanks!!

[Ashish_Mathur]

Hi,

Based on the 3 tables that you have shared, show the expected result.

[pjotaramos01]

The idea is to get into something like this: 

Area	Sub Area	Training	Employees Eligible	Employees Trained
Sales	Region1	Negotiation Training	400	340
Sales	Region2	Negotiation Training	200	190
Sales	Region3	Negotiation Training	520	400
Operations	Plant1	CommunicationTraining	300	200
Operations	Plant2	CommunicationTraining	600	600
Operations	Plant3	CommunicationTraining	30	22

Employees Eligible would be a measure in HR Panel filtered by the Training Eligibility (area, sub area and employee level) in Training Panel. But, for example, as I have a lot of Employee levels in many lines, I'm struggling creating a DAX that can do this.

And then, show a table that contains every employee that should have done a training (based on the eligibility fields) with a status collumn 

Employee ID	Name	Area	Level	Training	Status
0201	John	Sales	FL	Negotiation	Completed
0223	Josh	Sales	FL	Negotiation	Completed
4500	Marie	Sales	FL	Negotiation	Not completed

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.