one slicer for multiple columns to find a specific combination of skills

[Wilm117]

Hi Everybody,

I want to develop one slicer for this, based on a selection of 3 different skills, to find the right person who has all the selected skills, regardless of the column in which this skill is placed.

I have tried several things, but I keep not getting the desired result and keep falling into the same 2 situations:

1. When selecting, for example: I have "Analytical ability" & "Integrity", I find all persons who possess "Analytical ability" OR "Integrity" instead of the persons "Analytical ability" AND "Integrity"
2. The tables and graphs shoot at BLANK and no longer display data, while I have made a selection of skills that I know that this combination does exist.

I have the following data:

Medewerker	Skill 1	Skill 2	Skill 3
Arjan	Environmental Awareness	analytical ability
Joost	Environmental Awareness	analytical ability	Integrity
Edwin	Leadership	Organizational oriented governance	Motivating
Gerrit	Anticipation	Leadership	Integrity
Ine	analytical ability	Integrity	Development specialist
Alex	Leadership	Development specialist	Quick learner
Inge	sensitivity	Quick learner	Organizational oriented governance
Ingrid	Network skills		analytical ability

So:

- When I select "Analytical ability", I only want to see Arjan, Joost, Ine and Ingrid.

- When I select "Analytical Ability" & "Integrity", I only want to see Joost and Ine

- When I select "Analytical Ability" & "Integrity" & "Environmental Awareness", I only want to see Joost

- When I make a selection of 3 skills of which the combination (regardless of the order of columns) does not exist, I do not want to see anyone.

à Example: Anticipation & Network Skills & Integrity = No result

Ideally I would like one slicer in which I can make a selection of 3 skills that only show me the people who have a combination of all these 3 skills. The slicer must therefore exclude people who do not meet my selected skills. In addition, it should also not matter whether in which columns the skills are located to find the right person.

If it is not possible to fit this in one slicer, it is also no problem if I always select one skill via 3 separate slicers. It is important that I can find a combination of skills, regardless of which column these skills are placed in.

Thanks!

[harshnathani · Accepted solution]

HI @Wilm117 .

You will need to unpivot your tables.

See if this helps you.

https://towardsdatascience.com/power-bi-implement-and-or-selection-53bf58143ea3

Regards,

Harsh Nathani

[CNENFRNL]

@Wilm117 , het is simpel als je een correcte datamodel bouwt.

First, unpivot the table in Power Query as DAX favors one-dimensional table! (necessary)

Secondly, extract unique items of medewerker/skill to form dimensions. (optional)

Then, a simple datamodel leads to a equally simple solution to your issue.

Selected Skills = COUNTROWS ( ALLSELECTED ( dSkill[Skill] ) )

CHK = IF ( COUNTROWS ( fCandidate ) = [Selected Skills], "Geschikt" )

Geschikte Kandidaten = 
CONCATENATEX (
    FILTER (
        DISTINCT ( dMedewerker ),
        CALCULATE ( COUNTROWS ( fCandidate ) ) = [Selected Skills]
    ),
    dMedewerker[Medewerker],
    UNICHAR ( 10 )
)

[jameszhang0805 · Accepted solution]

Try this code 

[harshnathani · Accepted solution]

HI @Wilm117 .

You will need to unpivot your tables.

See if this helps you.

https://towardsdatascience.com/power-bi-implement-and-or-selection-53bf58143ea3

Regards,

Harsh Nathani

[Wilm117]

Thank you for the advice, it worked!