cancel
Showing results for
Did you mean:
Regular Visitor

## Retrieve the number of occurrence

Hi,

I am stuck on this problem from last 2 days; have done it on excel, solved it mathematically on notepad but power bi continues to elude me.

Here is what i want to do:

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Retrieve the number of occurrence

Try this measure in a card visual:

```Measure =
VAR NumSlicedSkills_ =
COUNTROWS ( DISTINCT ( Table1[Skill] ) )
RETURN
SUMX (
DISTINCT ( Table1[Person] ),
INT ( CALCULATE ( COUNT ( Table1[Skill] ) ) = NumSlicedSkills_ )
)```

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

8 REPLIES 8
Super User

## Re: Retrieve the number of occurrence

Try this measure in a card visual:

```Measure =
VAR NumSlicedSkills_ =
COUNTROWS ( DISTINCT ( Table1[Skill] ) )
RETURN
SUMX (
DISTINCT ( Table1[Person] ),
INT ( CALCULATE ( COUNT ( Table1[Skill] ) ) = NumSlicedSkills_ )
)```

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Regular Visitor

## Re: Retrieve the number of occurrence

Hey,

It did not work 😞 It just returned all rows as per slicer.

Highlighted
Super User

## Re: Retrieve the number of occurrence

Yes it does. See it at work in the attached file.

Regular Visitor

## Re: Retrieve the number of occurrence

Oh yes! i was mistaken. I gave the incorrect column name from my dataset of 100 columns. You are a genius. thanks!!

Regular Visitor

## Re: Retrieve the number of occurrence

There is one more small issue. Using your measure, i created this 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]), [yourmeasure]))

The only issue is that it shows metrics on axis even if there is no value in a stacked chart. Example, if i show this data location wise, it will show all locations, irrespective of the data available on any of them. Chart looks unnecessarily empty. Is there a way out for this?

Even if i replace "" with 0, it wouldnt make much difference.

Also, if i add another metric to this graph, the measure that you wrote completely gets distorted. Example, if i bifurcate this data location wise, number doesnt show up correctly.

Just wanted to highlight that the skill filter is based on a different table (created from 2 tables) and hence, that might be messing up my numbers.
Super User

## Re: Retrieve the number of occurrence

Replace "" with BLANK()

I guess I would have to see the data model (or pbix) to be able to know what might be going on with that "distortion"

Cheers

Regular Visitor

## Re: Retrieve the number of occurrence

Sure. Thank you. That Blank() part worked amazingly! Cant really share the database; however, will try to explain it as best as i can:

3 databases:

Person-Skill

Skill

Skill -> Merge of skills from rest of the 2 databases

 Person-Skill A B

 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

This is one.

When i said it gets distorted, i meant the following:

 Skill database A B C D

 Person-Skill A B 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

Does it make sense?

Regular Visitor

## Re: Retrieve the number of occurrence

Hi,

Does anyone have a solution for this?

Announcements