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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
AlB
Super User
Super User

Hi @Anonymous 

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
AlB
Super User
Super User

Hi @Anonymous 

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

 

Anonymous
Not applicable

Hey,

 

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

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

Anonymous
Not applicable

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.

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

Hi,

 

Does anyone have a solution for this?

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors