Power BI – The Data Model – Using Relationships

In the last few lessons we’ve talked about what relationships are and a little about how they work but it’s all been quite theoretical.  In this lesson though we’re going to actually have a first look at what we can do now we’ve created our relationship and in this lesson we’ll demonstrate how we can use a field from one dataset to manipulate another dataset.

Ok we’ve got our Power BI file open on the Model View and it’s where we left off in the last lesson. Now let’s change this to the Report View where we’ve got a blank screen. So we’re going to create a quick visualisation (although of course we’ll go through visualisations in more detail as part of Stage 3 of the course) and what we’ll do is go to Visualisations here and add in a Table visualisation.

Now we can see over here on the righthand side that we’ve got the fields pane showing the four datasets attached to this data model. Now first of all I’m going to recreate part of the Retail Analysis dataset, by just bringing in the ItemID, and number of sales at the normal price made . And then I’m going to enlarge the visualisation just by going to the corners and dragging the visualisation outward. Ok of course this hasn’t got us any further than our original Retail Analysis report.

Now what I’m going to Do is remove the ItemID field from the report and then add in the ItemID field from the Item Dataset. And now you can see that the ItemID field has been added in and the report is exactly the same as it was when we had the Item ID field in from the Retail Analysis table.

Now that might not seem that exciting.

Afterall all we’ve done is taken out a field and replaced it with exactly the same field, but that same field is from an entirely separate report! And that’s the beauty of this ability to create relationships. Once the relationship is created you can take any field from either report and put them together in the same report as if they had been part of one dataset. In other words you can now use the fields from one dataset to manipulate another.

Now to demonstrate that, let’s say that we wanted to bring in the Category of the Items that we’ve sold but not have the ItemID in, so that we can see how many unit sales we’ve made in each category. Of course, our retail analysis dataset doesn’t have a category field, so we’ve got to rely on the category information from the Item dataset.

I’ll take off ItemID from the visualisation and instead put on Category. And there we go we’ve now summed the total amount of sales made by category in the retail analysis dataset. If we wanted to put in segment as well, then that’s fine, we can just drag and drop that in as well. And to make this more readable I could even flip this into a matrix table, where I can go up and down through the hierarchy of Category and Segment. But now we’re getting ahead of ourselves as we’ll be looking at visualisations in Section 3.

So you see how easy this is? Now the relationship is created I can bring in any field that I wish from either report and they’ll mesh together no problem. Now we’ll see this more, later in Stage 3 but we can employ the same tactic in any visualisation – our tables are now connected together and are like one big table of data!