Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Wilm117
Helper I
Helper I

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

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!

2 ACCEPTED SOLUTIONS
harshnathani
Community Champion
Community Champion

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

View solution in original post

jameszhang0805
Resolver IV
Resolver IV

Try this code 
2.gif

jameszhang0805_0-1613656856617.png

 

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

@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.

Screenshot 2021-02-18 162617.png

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 )
)

Screenshot 2021-02-18 162845.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

jameszhang0805
Resolver IV
Resolver IV

Try this code 
2.gif

jameszhang0805_0-1613656856617.png

 

harshnathani
Community Champion
Community Champion

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

Thank you for the advice, it worked! 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors