Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have a skills filter with around 400 skills. I have data in table with people and their related skills. Something like given below:
Person | Skill |
A | X |
B | Y |
C | Z |
A | Y |
A | Z |
B | X |
D | Z |
E | X |
F | Y |
F | Z |
D | X |
Now, when i select X & Y in the skill slicer, i want a person measure to give a value of 2 because only A & B have both the skills, instead it is giving me 5 by filtering on distinct values on person column who have either X or Y.
Measure suggested by someone on this community:
Measure = VAR NumSlicedSkills_ = COUNTROWS ( DISTINCT ( Table1[Skill] ) ) RETURN SUMX ( DISTINCT ( Table1[Person] ), INT ( CALCULATE ( COUNT ( Table1[Skill] ) ) = NumSlicedSkills_ ) )
Final measure:
Actual count = IF(AND(COUNTROWS(rows)=DISTINCTCOUNT(Table[Person]),Skills[Selectedskillcount]>1),"",IF(OR(Skills[Selectedskillcount]=1,COUNTROWS(ALL(Skills[Skills]))=Skills[Selectedskillcount]),DISTINCTCOUNT(Table[Person]), [abovemeasure]))
3 databases:
Person-Skill
Task-Skill
Skill
Skill -> Merge of skills from rest of the 2 databases
Person-Skill |
A |
B |
Task-Skill |
B |
C |
Skill database |
A |
B |
C |
Selected skills | Person-Skill - 67 rows | Task-Skill- 23 rows |
A | 3 | 0 |
B | 64 | 22 |
C | 0 | 1 |
Desired result: | 0 | 0 |
Current result: | 3 | 0 |
Another scenario:
Skill database |
A |
B |
C |
D |
Person-Skill |
A |
B |
D |
Task-Skill |
B |
C |
D |
Detail Skill Set | Level | Person-Skill - 113 rows |
A | Level 1 | 1 |
A | Level 2 | 21 |
A | Level 3 | 37 |
A | Level 4 | 5 |
B | Level 2 | 1 |
B | Level 3 | 2 |
D | Level 1 | 1 |
D | Level 2 | 11 |
D | Level 3 | 31 |
D | Level 4 | 3 |
Must have Skills | Level | Task-Skill - 79 rows |
A | Level 1 | 1 |
A | Level 2 | 8 |
A | Level 3 | 11 |
A | Level 4 | 2 |
C | Level 2 | 1 |
D | Level 1 | 2 |
D | Level 2 | 14 |
D | Level 3 | 32 |
D | Level 4 | 8 |
First graph - Level wise | Person-Skill | Level 2 | Level 3 | Level 4 |
Current result | 4 | 1 | 2 | 1 |
Second graph - Overall | Person-Skill |
Current result | 3 |
Person-Skill | Task-Skill | |
Desired result | 0 | 0 |
Solved! Go to Solution.
To achieve the desired result of counting people who have all selected skills using a slicer, you'll need to use a different approach with DAX measures. Here’s a solution that should work:
Step 1: Data Preparation
Ensure your data model is correctly set up with tables for Person-Skill and Skills.
Step 2: Create Measures
You need to create a series of measures to count the number of people with all the selected skills.
Measure 1: Count of Selected Skills
First, create a measure to count the number of selected skills in the slicer:
SelectedSkillCount = COUNTROWS(VALUES(Skills[Skill]))
Measure 2: People with All Selected Skills
Now, create a measure to count the number of people who have all the selected skills:
PeopleWithAllSelectedSkills =
VAR SelectedSkills = VALUES(Skills[Skill])
VAR SkillCount = COUNTROWS(SelectedSkills)
RETURN
CALCULATE(
COUNTROWS(
FILTER(
Person-Skill,
CALCULATE(
COUNTROWS(VALUES(Skills[Skill])),
KEEPFILTERS(Person-Skill[Skill] IN SelectedSkills)
) = SkillCount
)
)
)
This measure ensures that it only counts people who have all the skills selected in the slicer.
To achieve the desired result of counting people who have all selected skills using a slicer, you'll need to use a different approach with DAX measures. Here’s a solution that should work:
Step 1: Data Preparation
Ensure your data model is correctly set up with tables for Person-Skill and Skills.
Step 2: Create Measures
You need to create a series of measures to count the number of people with all the selected skills.
Measure 1: Count of Selected Skills
First, create a measure to count the number of selected skills in the slicer:
SelectedSkillCount = COUNTROWS(VALUES(Skills[Skill]))
Measure 2: People with All Selected Skills
Now, create a measure to count the number of people who have all the selected skills:
PeopleWithAllSelectedSkills =
VAR SelectedSkills = VALUES(Skills[Skill])
VAR SkillCount = COUNTROWS(SelectedSkills)
RETURN
CALCULATE(
COUNTROWS(
FILTER(
Person-Skill,
CALCULATE(
COUNTROWS(VALUES(Skills[Skill])),
KEEPFILTERS(Person-Skill[Skill] IN SelectedSkills)
) = SkillCount
)
)
)
This measure ensures that it only counts people who have all the skills selected in the slicer.
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
12 | |
12 | |
12 |