Now that I’ve discussed what data modeling is, it’s time to understand what Power BI relationships among different tables can do for your reports. You may watch the full video of this tutorial at the bottom of this blog.
Different data sources within your models need to have the right relationships to yield the right results. When you fail to understand the concept of these relationships, you will never get the insights that you need. That’s why these relationships are important.
What Power BI Relationships Are For
I always set up my data tables in the simplest way possible. I want everything to be self-explanatory to make it easier for other people to work on the same tables later on.
Below, for example, you can see that I have these index columns and some lookup tables.
In each table, I have an ID that relates it to the main fact table, which carries all transactional information. An example is the Location ID that I’ve highlighted below.
This means that if I jump into my Sales table and a relationship has been established between my Location and Sales tables, I can see a column corresponding to that in my Sales table.
Note that you don’t necessarily have to look for a column that has the word “ID” in its name. It could be a row called City Name, or something like that. As long as it’s data that you’re referencing, that’s okay regardless of what the name is.
What do these relationships do?
Lookup tables contain filtering information, while fact tables have all the transactional information. Basically, establishing a relationship allows you to send those filters into the fact table.
It’s going to create the context for every calculation I run in my reports. If you’ve seen reports in Power BI, you’ve probably seen how you can click on filters on graphs to change the results showing up in visualizations or in the report itself. That’s made possible by the relationships running in the background.
How To Build Relationships Across Tables
It’s critical that you create the right kind of relationships across the right columns. There are two ways to do this.
The first way to do it is to simply drag and drop the ID column into the fact table.
In my sample model below, I’m going to start with my Salesperson ID. It’s easy enough to figure out where I need to drop that ID column, seeing that there’s also a Sales Person ID column on the Sales table.
Once that’s done, it automatically creates this relationship between the Salespeople table and the Sales table.
Let me drill into this relationship before proceeding, especially since this is the kind of relationship you’ll be working with 95% of the time in Power BI.
This is what you call a “one-to-many” relationship.
Notice that the relationship has a number 1 on the end of the lookup table. This means that each piece of data is only represented once. In this case, we’re talking about each salesperson.
Following that relationship, you’ll see that it’s leading to a star on the fact table. This star means many.
When you think about it, a single salesperson can sell several times, right? This means that even if each salesperson is represented once in the lookup table, they may be seen many times on the fact table depending on how the data is filtered.
This is exactly why the lookup tables should only list unique values for each particular group. That’s why I have separate tables for Salespeople, Products, Locations, etc.
I’m going to build another relationship now, but this time, between Products and Sales. I’m going to use the same drag and drop method.
Once the line representing the relationship shows up, I can hover over it to highlight that relationship. This is a good way to check if you connected the right tables together, especially if you already have a number of lines flowing into the fact table.
The second way to build relationships is by using the Manage Relationships button on the Home ribbon.
Once I click on that, the window is going to show me the relationships I added earlier.
To add another relationship to this list, just click on New.
Then, it’s going to ask me to select which source table I should be using. I’m going to use the Dates table in this case.
When the table shows up, I’ll highlight the Date column, since this is what counts as our “ID” from this column.
Then, I’ll choose the fact table, which is my Sales table. I’ll highlight the Product Date column since this is what corresponds to the Date column from the Dates table.
Below cardinality, I’m going to choose One to many. Make sure that there’s a tick mark next to “Make this relationship active”. Then, I’ll click on OK.
Now, a relationship has been formed between the Dates and Sales table.
I’m going to create another relationship here for Locations.
Then, I’m going to do the same thing for Customers.
Now that all the necessary relationships have been formed, I have a waterfall of filters coming from my row of lookup tables on top going into my fact table at the bottom. Those arrows are always going to point to the many sides.
Conclusion
When you understand how relationships work, working with Power BI is going to be easier. Again, these relationships add context to every calculation being run in your reports. This means that as long as you have a basic understanding of how the data and its filters flow, it’s easier to figure out what measures you need to write.
Some people get confused just because they feel that their scenario is different. They feel that they require a much more complicated setup than this. The truth is, all you need is to simplify your data.
When you pinpoint the right lookup tables and zero in on the right fact table, you can have this same basic setup no matter how complicated you feel your situation is.
I’ve also dealt with really complex scenarios in Power BI. But at the core of most of those reports, I still have this same model philosophy. You can explore these complex scenarios once you’ve signed up for a membership with Enterprise DNA and gain access to more courses.
All the best,
Sam
[youtube https://youtu.be/xGFbLvs31Io?rel=0&w=784&h=441]
***** Related Links *****
Creating Virtual Relationships In Power BI Using The TREATAS Function
How To Work With Multiple Dates In Power BI
How To Calculate Occupancy Days Per Month In Power BI Using DAX
***** Related Course Modules *****
Advanced Data Transformations & Modeling
Mastering DAX Calculations
DAX Formula Deep Dives
***** Related Support Forum Posts *****
Table Relationships
Relationship Tables Are Grayed Out
Active Relationships Using DAX
For more relationships support queries to review see here…
