Using a Slicer to return ONLY those with all selections ticked

[Shawkins566]

Hi All,

I have a skills data list.  Each skill that a person has is a new row, so there could be 50 rows per person or 3, just depends how many they have selected.  All Skills data is in a single Column.

I am trying to build a table that shows a person and their availability based.  I have a slicer with the skills in it, when I select multiple, I only want to return those people who have all the skills e.g. if I select 3 skills, then only people with all 3 skills should be returned.  

I have a separate table with the unique list of skills and I have tried creating a measure (using ChatGPT) to filter the table but I can't get it to work.

Does anyone know the best way to go about this?

Many thanks

[DataNinja777 · Accepted solution]

Hi @Shawkins566 ,

To display only individuals who possess all the skills selected in a slicer, you can implement the following DAX measure in Power BI:

People With Selected Skills = 
VAR SelectedSkills = VALUES(SkillsTable[Skill]) -- List of selected skills
VAR TotalSelectedSkills = COUNTROWS(SelectedSkills)
VAR PersonSkills = CALCULATETABLE(
    VALUES(SkillsData[Skill]),
    ALL(SkillsTable[Skill]) -- Removes slicer filter to evaluate person skills independently
)
VAR MatchingSkills = COUNTROWS(
    INTERSECT(PersonSkills, SelectedSkills)
)
RETURN
    IF(MatchingSkills = TotalSelectedSkills, 1, blank())

This measure works by capturing the list of skills currently selected in the slicer, counting the number of selected skills, retrieving all skills associated with each person while ignoring slicer selections, and counting the number of overlapping skills between a person's skills and the selected skills. If the number of matching skills equals the total selected skills, the measure returns 1; otherwise, it returns BLANK().

To implement this measure, you need to set up your data model with two tables. The first table, the Skills Data Table, should contain columns for "Person" and "Skill," listing each skill a person has. The second table, the Unique Skills Table, should contain a unique list of skills. Establish a relationship between these two tables using the "Skill" column.

After the data model setup, create the measure in Power BI using the provided DAX formula. Once the measure is created, include it in a table visual. This table should display the "Person" column and include the measure People With Selected Skills. Finally, apply a visual-level filter to show only rows where People With Selected Skills equals 1. By following these steps, the table visual will display only those individuals who possess all the skills selected in the slicer.

I have attached an example pbix file.

Best regards,

[Shawkins566]

Thank you so much!

[Laxmanjatoth]

try this two measures ,

create first measure 
CALCULATE(
count(Skills[Skill]),
FILTER(Skills, Skills[Person] = MAX(Skills[Person]))



create first measure 
CALCULATE(
count(Skills[Skill]),
FILTER(Skills, Skills[Person] = MAX(Skills[Person]))


this is optional if you creat a measure you can 

HasAllSelectedSkills =
VAR SelectedSkills = VALUES(Skills[Skill])
VAR TotalSelectedSkills = COUNTROWS(SelectedSkills)
VAR PersonSkills =
CALCULATETABLE(
VALUES(Skills[Skill]),
FILTER(Skills, Skills[Person] = MAX(Skills[Person]))
)
VAR MatchingSkills =
COUNTROWS(INTERSECT(SelectedSkills, PersonSkills))
RETURN
MatchingSkills


please let me know if still help 

[Shawkins566]

It is showing me the number beside each person with how many skills they have which is great but I can't work out how to filter the table so that only the matching ones show. I may be selecting 2 skills, or maybe 6 so I want to make sure that if I select 6 that only the ones with 6 are shown in the table

[Laxmanjatoth]

What problem are you having after completing all the procedures as you indicated in the given scenario?

[Shawkins566]

If I select 2 skills, it is returning people who have both skills but also those who have one or the other skill.  I've tried using a measure like below:

HasAllSelectedSkills =
VAR SelectedSkills = VALUES(Skills[Skill])
VAR TotalSelectedSkills = COUNTROWS(SelectedSkills)
VAR PersonSkills = CALCULATETABLE(
VALUES(Skills[Skill]),
FILTER(
Skills,
Skills[Person] = MAX(Skills[Person])
)
)
VAR MatchingSkillsCount = COUNTROWS(
INTERSECT(SelectedSkills, PersonSkills)
)
RETURN
IF(MatchingSkillsCount = TotalSelectedSkills, 1, 0)

but it just turns everything into a 1, or everything into a 0 if I tweak it.  I'm doing something wrong but I don't know what.

[johnt75]

Have a look at https://www.sqlbi.com/articles/apply-and-logic-to-multiple-selection-in-dax-slicer/