DAX, find all employees with selected(or better) skillset.

[PBIAlpaca]

Hi,

I have the following table:

What I wanted to do was to create a measure that would show me only employees with a given skillset(so for example all who are expert at python AND good at scala.

What I have done:

1. Create the CONCAT column.

2. Create the following measures:

All Selected Tools = COUNTROWS(SUMMARIZE(ALLSELECTED(Employees), Employees[CONCAT]))

All Selected Tools Dev = CALCULATE(DISTINCTCOUNT(Employees[CONCAT]), ALLSELECTED(Employees[CONCAT]))

SELECTED = IF([All Selected Tools]=[All Selected Tools Dev], 1, 0)

3. Set filter on my visual selected = 1

However, what I would like to do now, is to find all employees who have the selected skillset OR better.

So in this case, I would like to see both employees A and B, since employee B is EXPERT in python(we select python = GOOD and scala = GOOD but EXPERT>GOOD).

I have added this column called 'LevelValue' to be able to measure that but now I really don't know how to go forward. Thank you for any hints. 

Here is the link to my file:

https://www.dropbox.com/s/nk6qw0sjz5ae6db/example.pbix?dl=0

[v-yangliu-msft · Accepted solution]

Hi  @PBIAlpaca  ,

Here are the steps you can follow：

1. Create calculated table.

Table = 'Employees'

2. Create measure.

Flag =
IF(
    MAX('Employees'[Tool]) in SELECTCOLUMNS('Table',"1",[Tool]),
    IF(
        MAX('Employees'[LevelValue])>=MAXX(FILTER('Table',[Tool]=MAX('Employees'[Tool])),[LevelValue]),1,0),0)

3. Put the flag into the filter and set flag = 1

4. Result.

You can downloaded PBIX file from here.

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.

[Jihwan_Kim]

Hi,

Please kindly check the below measure and pbix file link.

Level or better Select =

VAR levelvalue =

SELECTEDVALUE (

'Levels'[LevelValue],

0

)

VAR levelselect =

FILTER (

ALL ( 'Levels'[LevelValue] ),

'Levels'[LevelValue] >= levelvalue

)

VAR toolselect =

ALLSELECTED ( Tools[Tool] )

RETURN

IF (

NOT ISFILTERED ( Tools[Tool] )

&& NOT ISFILTERED ( 'Levels'[LevelValue] ),

BLANK (),

IF (

SELECTEDVALUE ( Employee[LevelValue] )

IN levelselect

&& SELECTEDVALUE ( Employee[Tool] )

IN toolselect,

1,

BLANK ()

)

)

https://www.dropbox.com/s/8odlt6ung1gbh1w/PBIAlpaca%202021%200321.pbix?dl=0 

Did I answer your question? Mark my post as a solution!

[PBIAlpaca]

Thank you, that's a very nice solution. Although my whole problem is that I still need to be able to select multiple combinations of specific tools & levels. So for example all employees who are Good (or better) at python AND Limited (or better) at Scala. Also to exclude the ones who have only one of the specified skills(as we are interested in a specific skillset).

[Anonymous]

Hi @PBIAlpaca 

Concat all the columns from this table, dont leave anything. Then download a custol visual text filter. Drag this column in this visual. Post this, write any skill set you will get the results.

Please mark this solution as accepted.