Original subtitles

Welcome to the section on relationships.

So previously in the course we did spend a bit of time on relationships when we used our data master

and we saw that we could have two different files and create a relationship between them.

However, there had to be some rules in place as to how we were going to be able to connect them.

So we're going to spend a little bit more time in this section just to understanding our relationships

and what we can do.

We're going to be using a new file of data for this section, so we're going to be looking at some human

resource data.

What we've got is that we've actually got an employee master file and then we've got a file that keeps

a list of all the training courses that the employees have been on.

So we're going to look at how we could use those two tables of data to be able to create our power by

our reports.

Now, you'll see that currently on the screen that we've got the Excel Open and we're having a look

at this employee master file.

So you see that the first sheet is our master.

And what we've got is we've got a list of all our employees and there's about 50 employees in this list.

There we go, 50 employees.

So each one has a unique employee ID go to who the employee is.

Birthdate, marital status, hire, date department, salary, job gate.

So there's a bit of information there about the employee.

Now, if we jump across, you'll see that there is another table of data called workshops.

And the workshops.

What we've got is a list of all the training courses that our employees have been on.

So if we have a look over here, we can see that a training course was run and we given it a course

code, what the course name was about.

But then we can see that there's a list of which employees actually went on to that course and we're

using the employee ID to be able to reference that.

So what we've got now is in the master file, we've got the employee ID and in the workshops we've got

the employee, and that is our primary key.

That is the way that we can link the two tables together.

Now in the workshops we can have an employee has been on many different training courses, whereas in

the master file an employee can only exist once.

So please remember that many to one relationship can only have the employee once.

They're actually only one employee, but they can go on many training courses.

So as I say, we're going to use this data in our power.

BI We're going to jump back to Power BI We're going to bring this data in.

They're going to start with a new Power BI.

So please just start with a new Power BI desktop.

Okay, so we're back in Power BI desktop now and we're going to use our import data from Excel.

So I'm just going to select this option.

So we're going to be selecting the employee master file.

So please click on that and then click on Open and you'll see then it will create the connection to

the file and we'll bring up our navigator.

And again, you can see that I've got two views.

I've got the table view and I've also got the sheets view.

So if we look at this, you'll see that this is my master sheet.

And if we look at our workshops, this is my workshop view.

So just select the first two tables that will bring in the master and the workshops.

In this case, I'm just going to load this data straight in.

So we're going to select load and we're going to bring the two tables of data in so you can see it creates

a connection.

Loading the data.

And as we see in previously, now under our fields, we've now got the two tables.

So you can open these up and you can see the different fields that are available to them.

And also, if you wanted to, you could change these.

So if you're not happy with the table name of Master, you could just go into this and remove the one,

make it just the master table could go into this workshop table when we move the two and we could just

have the actual workshop table itself.

Just remove that.

There we go.

Okay, so let's start off with the relationship.

So we've seen previously we can go to our navigation, we can go to a model.

And what it did was that it shows me both my tables and in this case, Microsoft has picked up that

there's actually a already a connection.

So you can see that it's already created a relationship between the two.

Now, if I go over the relationship, you can see that it's done in between the two employee IDs.

So it kind of figured out that there's a relationship and automatically created this.

However, I want to show you the different ways that we can create the relationship.

So I'm going to start by deleting this.

One of the ways I can delete this is to select the line, right click on it, and then I can just choose

the delete option.

I would want to delete the relationships.

Yes, I do.

Okay.

Now you remember from previous in the course that one of the ways we can create this is we can choose

the field.

Let's say we're choosing our employee ID field and I could just take it across and I can actually find

the other employee ID field in our workshops and I could drop it on there.

And you can see automatically now that the relationship has been created.

So the one is on this side, the relationship direction, the menu on this side.

However, what I want to do is also just show you other ways that you can create the relationship.

So I'm going to delete this delete relationship.

If you go up into your ribbon, you will see that you get managed relationships as an option.

So we're going to select, manage relationships and currently it tells me there's no relationship.

So we're going to say we want a new relationship.

First is which table do you want to use?

So we're going to use the master table.

Now, the way that you do this is you just choose your table and then you choose which field you want

to create the relationship with.

So in this case, we're not using employee name, but if you wanted to use employee name, you just

click on it or birth date.

In this case, though, we do want to use employee ID, so just make sure the employee ID is selected.

Just click on it.

Then we are using our other table as our workshops and it's the same logic.

You just pick which field you want to be the connector.

Now, again, in this case it's going to be employee.

Now you can see the commonality in this case.

We want to want to many relationship.

Also, it asked me what is the cross filter direction Now in this case we're going to keep it singles.

We only want the data to be able to move in one direction and we're going to make sure that the relationship

is active.

So we're going to click okay on that.

So now we've created the relationship.

At any point, if you wanted to, you could go back in and edit this and make any changes.

So we'll close that.

And now you can see that the relationships been shown, so you can just make your relationship through

different methods.

However, we now got the relationship in place.

So let's go back to our report.

Let's look at a table and let's say, for example, that we wanted to know what was the total training

cost by different departments.

So Department is in our master, so we select that our cost is in our workshop, we select that.

And now because we've got the relationship between the two tables, it's now able to calculate that

and to produce that.

So basically you can do this for any visualization.

Let's say, for example, you wanted a column chart, you wanted now to say it's going here and let's

say you wanted to see it by different job grades.

Drop that in your x axis.

Going to say you wanted to see the total cost drop in your Y axis.

And there we go.

You've now got the relationship between the two tables.

Okay.

So we're going to conclude the lesson there.

I will see you in the next one.