Complex skills matrix

[Anonymous]

Hello,

I am trying to build skill matrix for our organization. We have around 70 employees but multiple teams, each one having between 9 and 65 skills! Ideally, the data source would be an Excel file or SharePoint list (this is what I have experience in).

What we should be able to do is:

1. Edit the associated skills for each employee, i.e. assign skill levels from 1 to 4 and view total scores

2. View total scores at the skill level, e.g. the skill Excel in team Payroll - a total of 30 (the sum of the individual Excel skill scores within that team).

3. If an employee moved teams, they would have skills falling under more than 1 team and we would like to see that. For example, if I moved teams from Customer Service to Payroll and had skills in both areas, if we searched for all employees with Customer Service skills, I would like to appear there (in spite of the fact that I was no longer a part of Customer Service). I was thinking that if an employee left a team, their previous line manager can change their status via a drop-down to Out of Team, and then the new line manager can add them under their new team to start adding skills.

I can't get my head round the following: if each employee were to have a record/item per skill, the total items would be more than 2000, which slows down the app and causes delegation issues for many formulae. For points 1 and 2, separate Excel tables/SharePoint list per team would do the job, however, I cannot figure out how point 3 would work...

All I need is a hint about the initial structure of my data source.

Thank you in advance for your suggestions!

[WarrenBelz]

Hi @Anonymous ,

There probably many valid answers to this, depending on what and how you want to query. The more granular the data, the better the result, but as you have alluded, the 2000 item limit could be an issue if you cannot get a smaller subset from an delegable query.

You did not say how many skills were involved here. As you have mentioned, the combination of employee and skills would grow extensively. You obviously need a list for employees, but if you has less than (say) 12 skills, a list for each of these would be usable to cross-query.

I personally would use the combined list (person/skill) as both are text and delegable in queries, but I am sure there are non-delegable fields you have in mind to query.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous]

Hi @WarrenBelz ,

Thank you for your reply.

We've got in excess of 200 skills across the board, with one of the teams owning 66 of them...

I think we will just stick with separate team tables/lists as data sources and accept the drawback of not being able to view the full list of skills for an employee if they cross teams and then have separate screens for the different teams.

Kind regards,

Zara

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

One more suggestion if I may.

If you have two tables

• a skills table containing the employee
• an employee table which contains the team

you should be able to query anything. I actually use something very similar in a training process.

You can look from the qualifications/skills to see which employees have them, including which team they are in, the team (I have crews) and list all the employees with whatever skills they have and query obviously the employee.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous]

Hello @WarrenBelz ,

Thank you for this suggestion. I will see how this will go :).

Kind regards,

Zara

[mkusler]

@WarrenBelz 

I know this is an old post, but it sounds like something similar to what I am trying to setup. 

I've been asked to take an Excel spreadsheet and create a PowerApp that is for viewing purposes only. The source data is structured in a manner like this (The list of employees is quite long):

I know that using Excel isn't the best idea, but with the way the data is currently structured, is it possible to create a PowerApp that does the following:

1. Able to display all employees by any combination of Work Area, Competency Area and Competency Element

2. Able to choose a single employee and see all of their Competency Areas and Competency Elements, and Ratings.

Is any of this possible with the current data structure? I know having the employee names as headers isn't helpful.

Probably the biggest question I have is, If I were able to convince the department to move to SharePoint List(s), what would be the best way to setup the lists to achieve the goals above? I have never attempted to work off two lists and am unsure how to relate them.

Thanks!

[Joyel_Paul]

Did you get the desired Matrix, i am also trying something similar and i am using Multiple SharePoint as Data Source, one for skills and one for Employee Details, I could achieve Matrix like view using galleries, but the skills proficiency is only visible when I click on a certain employee. I have earlier created using a single SharePoint database. But while using headers and Lines as different table i couldn't Showcase skill proficiency of all employees all at once, maybe my Power fx code uses "ThisItem.Skillproficency" thus only one employee's skill is displayed. 

[WarrenBelz]

@mkusler ,

You are correct that Excel is not the best idea as (among many other things), the more complex the query and/or the bigger the data set, things that should work do not. The first thing I would do with your structure would be do have a separate record for each Employee/Work Area/Competency Area/Competency Element combination - this will make the queries you need much simpler. SharePoint lists are really no different in structure to the Excel Table you have posted and are queried with the same code structure.