topic Re: Number of occurences in DAX Commands and Tips

Number of occurences

Anonymous — Thu, 12 Dec 2019 07:22:51 GMT

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.

Re: Number of occurences

Anonymous — Thu, 12 Dec 2019 11:46:03 GMT

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))

Re: Number of occurences

Anonymous — Fri, 27 Dec 2019 10:00:54 GMT

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.

Re: Number of occurences

MartynRamsden — Fri, 27 Dec 2019 12:09:32 GMT

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.

Re: Number of occurences

Anonymous — Mon, 30 Dec 2019 05:38:46 GMT

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.

Re: Number of occurences

Anonymous — Tue, 31 Dec 2019 07:47:46 GMT

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.

Re: Number of occurences

Anonymous — Fri, 17 Jan 2020 06:56:33 GMT

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.

Re: Number of occurences

Anonymous — Fri, 17 Jan 2020 09:03:41 GMT

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

Re: Number of occurences

Anonymous — Fri, 17 Jan 2020 09:21:07 GMT

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

Re: Number of occurences

Anonymous — Fri, 17 Jan 2020 09:49:32 GMT

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.

Re: Number of occurences

Anonymous — Fri, 17 Jan 2020 09:51:30 GMT

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

Re: Number of occurences

Anonymous — Mon, 20 Jan 2020 09:46:12 GMT

Hi,

Does anyone has a solution for this weird issue?

Re: Number of occurences

Anonymous — Mon, 20 Jan 2020 10:45:11 GMT

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.

Re: Number of occurences

Anonymous — Thu, 23 Jan 2020 03:35:00 GMT

Hey,

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

Re: Number of occurences

Anonymous — Thu, 23 Jan 2020 05:18:39 GMT

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.

Re: Number of occurences

Anonymous — Thu, 23 Jan 2020 05:30:21 GMT

Re: Number of occurences

Anonymous — Thu, 23 Jan 2020 08:07:58 GMT

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.

Re: Number of occurences

Anonymous — Thu, 23 Jan 2020 14:12:13 GMT

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.

Re: Number of occurences

Anonymous — Thu, 23 Jan 2020 14:53:57 GMT

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.

Re: Number of occurences

Anonymous — Wed, 12 Feb 2020 08:35:15 GMT

Thank you! This works like wonder.