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