Combining data from two sources

[Anonymous]

Hi Guys,

I am working on a Team Skills Powerapp that connects with a few SharePoint lists. From what I can see Power BI tiles can be used quite well to display the analytics behind it. I only got access to Power BI Desktop earlier this week and I am absolute beginner. However, I did manage to produce partly what we need (a matrix for point 1 below ) and I am quite happy with the result. Now I am trying to solve the second part...

My data sources are as follow:

1. Team Skills Matrix that shows the employee skill levels for their team only with the following structure (I have separate sharepoint lists for each team, as we have many teams with a long list of different skills each)

Employee	Skill A	Skill B	Skill C
Employee 1	4	0	5
Employee 2	3	1	2
Employee 3	2	3	1

1. Skills master list (1 for all teams) that show the following in the following structure

Team	Skill	Skill Requirement for daily activities	Skill Required for cover/attrition
Team A	Skill A	3	2
Team A	Skill B	3	3
Team A	Skill C	4	2
Team B	Skill D	4	3

1. What I need analytics-wise is one or two tiles maximum for each team that would be a combination of the two sources above : (Please note that the Skill Headers in the skill matrix lists are identical to the content in the Skill column in list two, so index and match works if I export the content of my lists to Excel)

Employee	Skill A	Skill B	Skill C
Employee 1	4	0	5
Employee 2	3	1	2
Employee 3	2	3	1
Total Skill	Skill A Total	Skill B Total	Skill C Total
Skill Requirement for daily activities	Look Up value for skill A from source 2	Value for skill B from source 2	Value for skill C from source 2
Skill Required for cover/attrition	Value for skill A from source 2	Value for skill B from source 2	Value for skill C from source 2
Total Skill Requirements	Sum of the above 2 rows	Sum of the above 2 rows	Sum of the above 2 rows
Requirements met?	Count how many  2’s and 3’s we have in the 1st data source, compare them against the Total Skill requirement. If skills available <total skills requirement, format in red, otherwise green.	Count how many  2’s and 3’s we have in the 1st data source, compare them against the Total Skill requirement. If skills available <total skills requirement, format in red, otherwise green.	Count how many  2’s and 3’s we have in the 1st data source, compare them against the Total Skill requirement. If skills available <total skills requirement, format in red, otherwise green.

I would be extremely grateful if I got some hints on how to proceed...

Thank you in advance!

Kind regards,

Zara

[v-alq-msft]

Hi, @Anonymous 

Based on your description, you need to go to Power Query, make 'SkillA','SkillB','SkillC' selected and click 'unpivot columns' in the 'Transform' ribbon. Then you may click 'Close and Apply'.

There is a many-to-one relationship between 'Team Skills' and 'Skill master'.

Then you may create four measures as below.

Skill Requirement for daily activities = 
var _skill = SELECTEDVALUE('Team Skills'[Skill])
return
LOOKUPVALUE(
    'Skills master'[Skill Requirement for daily activities],
    'Skills master'[Skill],
    _skill
)

Skill Required for cover/attritio = 
var _skill = SELECTEDVALUE('Team Skills'[Skill])
return
LOOKUPVALUE(
    'Skills master'[Skill Required for cover/attrition],
    'Skills master'[Skill],
    _skill
)

Total Skill Requirement = 
SUMX(
    'Team Skills',
    'Team Skills'[Skill Requirement for daily activities]+'Team Skills'[Skill Required for cover/attritio]
)

Format = 
IF(
    SELECTEDVALUE('Team Skills'[Value])<[Total Skill Requirement],
    1,0
)

Then you need to go to Visualizations ribbon, click 'Conditional formatting', make 'Value' selected, check 'Background color' on, click 'Advanced controls' and set as below.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-alq-msft

Thank you. This is very useful indeed. Is there any way to display the calculated measures as rows?

Kind regards,

Zara

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You need to make the visual selected, go to 'Visualizations' ribbon, click 'Values', set 'Show on rows' as on.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft Thank you!

[amitchandak]

@Anonymous 

First, you need to unpivot the first table

https://radacad.com/pivot-and-unpivot-with-power-bi

Post that you need to append of merge as per need

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Also, Refer https://docs.microsoft.com/en-us/power-bi/guidance/