Adding a column with values from different tables based on a condition

[Ammar-Jaradat]

Hello There!

I have a problem and trying to figure out a solution for it.

I have a set of data ( From different columns) that i want to show in one table, but conditional based on a single Value which is Staff name.

For example, i have the following data set : 

1- Closed Items-- Table 1,Contains all Results for all team members per day

2- Completion %--- Table 2,Contains all Results for all team members per day

3- Satisfaction%-- Table 3, Contains all Results for all team members per day

What i need is to ceate a new table similar to the below

Staff Name	Closed Items	AVG Completion %	AVG Satisfaction%
John	10	95%	94.1%
Mark	20	80%	93.7%
Marie	30	100%	98.3%

Appreciate if you can assist me with this one 

[BA_Pete · Accepted solution]

Hi @Ammar-Jaradat ,

You need to create a STAR SCHEMA data model.

1) Create an employee dimension table - a table that contains a unique list of all employees (names and/or ID numbers).

2) Apply all your tables to the data model and make the following relationships:

- dimEmployee[E'ee Name or ID] ONE : MANY closedItems[E'ee Name or ID]

- dimEmployee[E'ee Name or ID] ONE : MANY completionPct[E'ee Name or ID]

- dimEmployee[E'ee Name or ID] ONE : MANY satisfactionPct[E'ee Name or ID]

3) Use dimEmploye[Name] in your visuals and bring in the fact metrics from each of your fact tables.

Reference:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema 

Pete