New to Power BI and DAX and need help

[prabin09]

Hello everyone, I am new to Power BI and DAX. I am using Power BI for one my work project. Basically, I have 3 tables: Employee, Skill, and Language.
Employee table contains all the information of an employee.
An employee can have multiple skills, so the skill table contains employee id, skill, and country the employee is located in.
An employee can also speak multiple languages, so the language table contains employee id, language, and country the employee is located in.

There is a one to many relationship between Employee and Skill table. Same goes for the Employee and language table.

I have a task to create a Dashboard where users can filter employees based on skills and languages. I am able to create dashboard that can accomplish some of the objectives. The dashboard that I have can filter employee on multiple skills and languages using the AND logic. For example, if I want to look employees that have Java and Python skills as well as speak spanish then I am able to achieve that.

But the problem is, skills and language data are not in standard form. So, there are employee that have listed Java skill as Java Programming, Core Java, Java 7 and so on. This goes for all the skills and its difficult to standardize all the variations at this point. So, I was wondering if I could perform %LIKE% search without affecting the intersection logic. If I select Python then it should also match Python 3.0, Basics of Python, and so on. 

This is how my dashboard looks like.

To apply the AND logic for skills slicer, I have following DAX measures:

Selected Skills = IF(ISFILTERED(Skill[Skill]), COUNTROWS(ALLSELECTED(Skill[Skill])), 0)

Employee Skills Check =

IF([Selected Skills] = 0, 1,

 IF(DISTINCTCOUNT(Skill[Skill]) = [Selected Skills], 1, 0))

Slicer Count for Skill = CALCULATE(COUNTROWS(Employee), FILTER(Employee, [Employee Skills Check] = 1))

Do I have to modify one of these DAX measures for the %Like% search or create new ones?

Similar logic and DAX measures apply for language. When I combine slicer count for skill and slicer count for language as filters to a visula making the condition where value > 0, then it gives employees that have skills as well as languages (intersection between languages and skills too).
Can you guys please help me to solve this?

This is how employee table looks like:

Employee_IDEmployee_NameEmailJob_TitleJob_LevelCountry

1	Lila Mercer	e.richardson@randatmail.com	Associate Application Developer	2	Chile
2	Orion Wolfe	b.bennett@randatmail.com	Associate Application Designer	9	Argentina
3	Serena Blake	e.moore@randatmail.com	Professional Application Developer	10	Uruguay
4	Zephyr Reed	k.lloyd@randatmail.com	Professional Application Designer	8	Brazil
5	Aria Donovan	f.edwards@randatmail.com	Associate Application Developer	2	Colombia
6	Kian Patel	k.perkins@randatmail.com	Associate Application Designer	4	India
7	Lyric Hayes	j.richards@randatmail.com	Professional Application Developer	2	Argentina
8	Emery Yang	a.reed@randatmail.com	Professional Application Designer	0	Armenia
9	Mira Bishop	o.tucker@randatmail.com	Associate Application Developer	12	Australia
10	Ronin Park	c.carter@randatmail.com	Associate Application Designer	4	Austria

 This is how skill table looks like:
Employee_IDSkillCountry

1	Python	Chile
1	Java	Chile
1	C++	Chile
1	JavaScript	Chile
1	React	Chile
1	Angular	Chile
2	Django	Argentina
2	Core Java	Argentina
2	Node.js	Argentina
2	Python 3.0	Argentina
3	Java Programming	Uruguay
3	Java 7	Uruguay
3	Basics of Python	Uruguay

When I select Java in the slicer it should also account Java Programming and count it. The count for employees with Java and Python skills should be 3 instead of 1.  This logic should be implemented without affecting the intersection logic.

[Ashish_Mathur]

Hi,

In the Skill table, you should write this calculated column formula to group the various skills into categories.  Create a single column Table (called Skill category) with 2 entries - Python and Java.  Write this calculated column formula in the Skill table

Column = FIRSTNONBLANK(FILTER(VALUES('Skill Category'[Skill category]),SEARCH('Skill Category'[Skill category],Skill[Skill],1,0)),1)

Hope this helps.

[prabin09]

Hello @Ashish_Mathur ,
Thank you so much for your reply. I am concerned that this won't be an effiecient solution if I have hundreds and thousands of employees data, and each employee has multiple skills with multiple names and variations. Also, theere will be an update to the table every month. So, I guess with this solution I will have to check in everytime if I have a skill category that will include a new employee skill and if not I will have to keep adding new skill categories. Is that correct? 

[Ashish_Mathur]

You are welcome.  You will obviously have to create the Skill Table manually.  A human being will have to put in some effort at least.  

[hnguy71]

hi @prabin09 ,

You can go about this in three or more ways, but all of them requires a mapping of some sort:

1. Easy way is to use groups within Power BI.

2. Medium way is to have a master mapping to your skills.

3. Long way is to call an API or ML model to determine category / bin of your skills or languages.

I'll show you the easy way first as it may fit your criteria:

 

[prabin09]

Hello @hnguy71 ,

Thank you for your reply. I see that your 1st solution is to group skills into categories. This could have been a solution for me if I had limited skills, but the problem is I have about hundreds and thousands of rows of data. It wouldn't be efficient enough to manually group these skills. Also, every month I will be getting new data in a huge amount. So, this solution is not that applicable to my case. Above is just a sample data where I have tried my best to replicate the challenges that I am having. 
I would prefer a solution where when users filter multiple skills (Java, Python) and matchmaking happens, then other variations (Core Java, Python 3.0) should have a true match to contribute to the employee count.