In today’s tutorial, I want to highlight how important it is to build your data model relationships correctly.
Data modelling is one of the foundations of your Power BI report so it’s necessary to set it up correctly.
Previously, I discussed some techniques that can help you manage your model right. One of them is about building the relationships of all your existing tables inside Power BI.
In this tutorial, I want to teach you how to properly set up your data connections. First, make sure that you have already optimized your tables. If you want to check out more techniques in managing your tables, you can go here.
Creating One-To-Many Data Model Relationships
Make sure to place the lookup tables at the top and the fact table below. This is how you should always start your data model relationships. You can easily create the relationship when you drag and drop any column from one table to another.
In the example, you can see that the Customers lookup table is connected to the Sales fact table. If you look more closely, you’ll notice that the arrow comes from the Customers table and goes down to the Sales table.
This is an example of a one-to-many relationship. In this data model relationship, the customer data from the Customers table is only referenced once. Meanwhile, the customer data from the Sales table is referenced many times.
This is why it’s the many sides which are represented by a star. The arrow also signifies the direction in which the filter will flow.
You can also link the Date column from the Dates table to the Order Date column of the Sales table to create another one-to-many relationship.
This time, you can bring the Index column from the Products table to the Product Description Index column of the Sales table.
Additionally, you can link the Index column from the Regions table to the Delivery Region Index column of the Sales table.
This is how you should set up your data model properly. When you follow this technique, you can efficiently build your data model and create a great Power BI report later on. Don’t worry too much about making it perfect, just make sure to do things efficiently.
Lastly, you can create another one-to-many relationship when you drag the Channel column from the Channels table to the Channel column of the Sales table.
Setting Up Many-To-One Data Model Relationships
Now that you have learned about the one-to-many kind of data relationships, I’ll also teach you about the many-to-one relationship.
To manage your existing data relationships, you can double-click on one of the lines. Another way is to click Manage Relationships.
In the Edit relationship window, you can see connected tables and columns. For instance, you can see the Customer Name Index of the Sales table and the Customer Index column of the Customers table. In the Cardinality section, you can see that it has a many-to-one data relationship.
This kind of data model relationship is clearly different from the first one that I discussed earlier. The many-to-one data relationship allows any filter to go in both directions.
In the Cross filter direction section, you can either select Single or Both. In this case, you need to select Single because selecting a double filter can be very confusing.
Using one-to-many data relationships will prevent you from getting weird results in your report. If you want to make your data model simple, it’s okay to keep using the one-to-many relationship.
***** Related Links *****
Top 3 Best Practices To Organize Your Power BI Models
Creating Virtual Relationships In Power BI Using The TREATAS Function
How To Work With Multiple Dates In Power BI
Conclusion
No matter what business information you’re dealing with, make sure to follow the basic data model structure.
I hope you learned something new about data modelling. I have mentioned the two types of data model relationships: one-to-many and many-to-one.
However, there are still other types of data relationships that you can encounter. For instance, one-to-one and many-to-many. But don’t worry, you just need to understand these ones for now.
I hope that data modelling made more sense after this tutorial. If you want to get more information and similar scenarios for your data model, check out the Enterprise DNA support forum.
Thanks!
Sam
