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

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

Reply
Anonymous
Not applicable

Retrieve number of occurence in data

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:

 

PersonSkill
AX
BY
CZ
AY
AZ
BX
DZ
EX
FY
FZ
DX

 

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 skillsPerson-Skill - 67 rowsTask-Skill- 23 rows
A30
B6422
C01

 

Desired result:00

 

Current result:30

 

 

Another scenario:

 

Skill database
A
B
C
D

 

Person-Skill
A
B

D

 

Task-Skill
B
C
D

 

Detail Skill SetLevelPerson-Skill - 113 rows
ALevel 11
ALevel 221
ALevel 337
ALevel 45
BLevel 21
BLevel 32
DLevel 11
DLevel 211
DLevel 331
DLevel 43

 

Must have SkillsLevelTask-Skill - 79 rows
ALevel 11
ALevel 28
ALevel 311
ALevel 42
CLevel 21
DLevel 12
DLevel 214
DLevel 332
DLevel 48

 

First graph - Level wisePerson-SkillLevel 2Level 3Level 4
Current result4121

 

Second graph - OverallPerson-Skill
Current result3

 

 Person-SkillTask-Skill
Desired result00

 

1 ACCEPTED SOLUTION
technolog
Super User
Super User

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.

View solution in original post

1 REPLY 1
technolog
Super User
Super User

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors