How to get employees list who are missing the selected competency

[aswanim]

Hi 

I have a employee table and their competency list. 

employee name	Competency
alex	Class A-Manually movable personal lifts
alex	Class B self-propelled personal lifts
alex	Health check
Scott	Class A-Manually movable personal lifts
Scott	Health check
Lisa	Building permit
Lisa	Health check

If user selects "Class B self-propelled personal lifts" competency ,(here competency is slicer), then the report should shows the list of employees who are missing that competency. so the report should show the employees  as Scott , Lisa. Can you please help me how i can implement this.

Thanks.

[v-yangliu-msft · Accepted solution]

Hi  @aswanim ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Competency])

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Table 2'[Competency])
var _column=SELECTCOLUMNS(FILTER('Table','Table'[Competency]=_select),"com",[employee name])
return
IF(
    NOT(MAX('Table'[employee name])) in _column,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[aswanim]

Hi @v-yangliu-msft ,

Thanks for your reply. I am writing more clear on my requirement.

Here in your solution the competency which we have taken in table 2 is independent list. But i have existing reports where they have a competency and this report should have interaction with that competency. In that way this doesnt work here. 

One more thing is that the  employee and competency are in different tables and which is getting joined based on a emp_comp table.

samle data is below.

emp
emp name	emp_id
alex	1
Scott	2
Lisa	3

emp_comp
emp_id	comp_id
1	1
1	2
1	3
2	1
2	3
3	3
3	4

competency
comp_id	type
1	Class A-Manually movable personal lifts
2	Class B self-propelled personal lifts
3	Health check
4	Building permit

Can you please help me with this.

Thanks

[amitchandak]

@aswanim, You need an independent table

Power BI Exclude Slicer value. Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE