Number of occurences

[Anonymous]

Hi,

I am stuck on this problem from sometime. 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. 

[Anonymous]

I am stuck on this problem from sometime. 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. 

[Anonymous]

@Anonymous 
Please follow this steps

1. Create a table from modeling tab

DimSkill = VALUES(Test[Skill])

2. Create a flag (measure) to check if a person have selected skills

Flag = 
VAR _skill = CALCULATE(CONCATENATEX('Test',Test[Skill]," "),ALLEXCEPT(Test,Test[Person]))
VAR _flag = IF(FIND("FALSE",CONCATENATEX(DimSkill,CONTAINSSTRING(_skill,DimSkill[Skill])," "),1,0)=0,1,0)
RETURN _flag

 3. Finally create one more measure to get totals correct

Measure = 
VAR _table = SUMMARIZE(Test,Test[Person],"IsSkilled",[Flag])
RETURN COUNTROWS(FILTER(_table,[IsSkilled]=1))

[Anonymous]

Hi Vimal,

Thank you for your response; however, it did not solve my purpose.

If i select skills A & B, result should give me count of colleagues who have BOTH the skills.

[MartynRamsden]

Hi @Anonymous 

Try adding this as a measure:

People With Skills = 
VAR SelSkills = VALUES ( 'Test'[Skill] )
VAR NoSelSkills = COUNTROWS ( SelSkills )
VAR PeopleWithSkills =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( 'Test', 'Test'[Person] ),
            "@NoOfSkills", 
                CALCULATE ( COUNTROWS ( FILTER ( 'Test', 'Test'[Skill] IN SelSkills ) ) )
        ),
        [@NoOfSkills] = NoSelSkills
    )
VAR Result = COUNTROWS ( PeopleWithSkills )
RETURN Result

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[Anonymous]

Thank you Martyn, but I am unable to get the correct answer through this measure. Just to clarify, i have 3 tables: Skills, People category A, people category B. Skills table has concat of skills from both the other tables. So if i apply this measure that you have suggested individually referring to these tables, i dont get the right answer. Please note that i have created relationships between skills and rest of the two tables basis skills column.

[Anonymous]

HI @Anonymous 

Create one dummy table with all your distinct skills.

Don't make relationship of that table with any other table.

Create measure=

SelectedSkills = CONCATENATEX(ALLSELECTED('Dummy Skill'[Skill]),'Dummy Skill'[Skill],",",'Dummy Skill'[Skill])

This will give you  Comma seperated selected skills.

Create one more table

Summerized Employee = SUMMARIZE(Employye,Employye[Person],"ALLSkill",CONCATENATEX(ALLSELECTED(Employye[Skill]),Employye[Skill],",",Employye[Skill])).

This will give you employye person with all skills he has in comma seperation.

Now create one more measure in this table.

SkillValidation =

SUMX('Summerized Employee',IF(CONTAINSSTRING('Summerized Employee'[ALLSkill],[SelectedSkills])=TRUE,1,0))

and filter this measure in visual and set not to 0.

Thanks & regards,

Pravin Wattamwar

www.linkedin.com/in/pravin-p-wattamwar

If i solved your problem Give kudos and mark it as solution.

[Anonymous]

Thank you Pravin. This solution works as long as the skills are together. If there is even a skill in between the 2, measure doesnt read the same and returns a 0.

[Anonymous]

Hi @Anonymous 

Try this

Create new table

AllSKill = VALUES(Sheet1[Skill])

Add this column in slicer

Create below  measures

TotalSelectedSkills = COUNT(AllSKill[Skill])

Total matching Skills = CALCULATE(COUNT(Sheet1[Skill]),FILTER(all(Sheet1[Skill]),Sheet1[Skill] in ALLSELECTED(AllSKill[Skill])))

Flag = IF([Total matching Skills]=[TotalSelectedSkills],1,0)

Add flag measure to visual level filter and set it to 1.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Hey,

Spoiler

Total matching skills measure gives the total of all colleagues who have either of the selected skills and since this wahy it wont be equal to TotalSelectedSkills, flag will stay 0

[Anonymous]

Let say u have selected 2 skills selected in slicer.
Total matching skill check for particular emp does he have all the selected skills n if both are equal then set flag 1.

Try this solution in your desktop..if you face any issue let me know.

[Anonymous]

[Anonymous]

You need to add it in table not in card.

Card will give you overall summerize answer.

Drag Name,Skills and above two measures in table visual. and filter measure in visual level filter and set it to 1.

The table will display name of candidates having all the skills selected in slicer.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Oh. Thank you. It does work in a table. However, my motive is to show this as a number because i cannot show the names to the audience. That is a little confidential.

[Anonymous · Accepted solution]

Hi @Anonymous 

Create table

ALLSKILL=values(Sheet1[Skill])

Create measure

Flag_ =

var TotalSelectedSkills = COUNT(AllSKill[Skill])

var TotalMatchingSkills = CALCULATE(COUNT(Sheet1[Skill]),FILTER(all(Sheet1[Skill]),Sheet1[Skill] in ALLSELECTED(AllSKill[Skill])))

Return

IF(TotalmatchingSkills=TotalSelectedSkills,1,0)

Then create one more measure

Total =

var tab=SUMMARIZE(Sheet1,Sheet1[Person],"Flag",[Flag_])

return

COUNTROWS(FILTER(tab,[Flag]=1))

Total will give you distinct count of employees.

Still facing issues let me know.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Thank you! This works like wonder.

[Anonymous]

i didn't get your point. Could you please explain what is the issue?

[Anonymous]

Sure. If i go by the example that you gave:

Chosen skills: X,Y

Person A: X,Y,Z

Person B: X,Z,Y

Person A is coming as 1 and B as 0

[Anonymous]

if you see concatenation. i have sorted it by skills so you will get X,Y,Z only. not X,Z,Y.

Thanks & regards,

Pravin Wattamwar

If it resolve your problem mark it as a solution and give kudos.

[Anonymous]

Okay. Lets say, i selected X & Z. Y will always come in between, if a person has Y and then it will count him as 0

[Anonymous]

Hi,

Does anyone has a solution for this weird issue?