Retrieve number of occurence in data

[Anonymous]

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

[technolog · Accepted 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.