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
Highlighted
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
Highlighted
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?

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.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

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