cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shefali_O Regular Visitor
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:

 

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Retrieve the number of occurrence

Hi @Shefali_O 

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  Datanaut

 

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Retrieve the number of occurrence

Hi @Shefali_O 

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  Datanaut

 

View solution in original post

Shefali_O Regular Visitor
Regular Visitor

Re: Retrieve the number of occurrence

Hey,

 

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

Super User
Super User

Re: Retrieve the number of occurrence

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

Shefali_O Regular Visitor
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!!

Shefali_O Regular Visitor
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
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

Shefali_O Regular Visitor
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

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

This is one.

 

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

 

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

 

Does it make sense?

Highlighted
Shefali_O Regular Visitor
Regular Visitor

Re: Retrieve the number of occurrence

Hi,

 

Does anyone have a solution for this?

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,704)