Hello guys, hope you are doing well. Today, I’m going to discuss with you the relationship feature of Power BI. Before going in deep of this topic, I want to describe some important concepts of Power BI. Power BI is a very Intelligent tool developed by Microsoft company. The reason behind the creation of Power BI is to provide the user with an easy way to generate meaning from the garbage data in the form of reports and dashboards. Reports and dashboards are the way to show the data using graphs and other visuals provided by Microsoft Power BI tool. To implement or to create the reports, first, we need to import the data set into Power BI desktop.
BI provides many features to manipulate the data set like Power query also you can create a new column and table to the existing data set. Power BI provides filters to filter the data as per the requirement. It contains many different visuals to create reports or dashboards. The report contains many pages and Dashboard contains many reports. You can also apply RLS to the data set to secure or restrict the data. By using RLS you can easily restrict users to see the specific data. Power BI also provides relationship feature to create a relationship between one or more tables.
We can easily understand the data when it is presented using graphs and charts because we can not see or don’t want to see the data in the tabular or matrix form when we have large amount of data. Generally, charts and graphs are more attractive than the data in tabular or matrix form. That’s why it good to use charts or graphs rather than tables or matrices. Now relationships are not necessary but we can make use of it to show the data as required or as we want. Let’s start discussing the relationships in Power BI. We mainly focus on Many-to-Many relationships in Power BI.
Power BI relationship feature allows you to join tables that have relationship (Cardinality) of many-to-many. Also you can easily connect many data sources at one time. Many-to-Many relationship is a part of larger composite model capabilities in power BI desktop.
In the above picture, you can easily see that we are creating many-to-many cardinality or relationship. Also, it shows me an alert or warning that this relationship should only be used if it is expected that neither column contains (date and date) unique values, and that the significantly different behavior of Many-many relationships is understood.
This Many-to-Many relationship feature is a part of the below three related features.
Composite Models: This Model allows us to create a report with more than two data connections that includes Direct query or import.
Many-to-Many Relationships: With the help of the composite model you can easily create many-to-many relationships between tables. Because of this, we no longer need to have unique values in the tables. Also, because of this, we don’t need to create an extra table to create relationships.
Storage Mode: This is the feature where you can easily specify which visuals require a query to back-end data sources. Because of this feature, the performance increases and back-end load reduces.
Jump to Section
How to enable many-to-many relationships to preview feature?
To enable many-to-many relationships preview feature in Power BI desktop, you need to follow the following steps:-
1. Click on the file icon at the top-left of the Power BI Desktop. Also below is the screen-shot of the same.
2. Now you can see the Options and settings option just click on that and you will get the screen as below.
3. Just click on the Options button as shown above. After clicking on that button you can now see the options window where you need to click on the preview feature option then you can see the check boxes as shown below.
4. Here, you can see that I have already selected the Composite Models option. Now, just click on the “Ok” button. Once you click on the button, it will ask you to restart the Power BI Desktop as shown below.
Improvements After Introduction Many-to-Many relationship feature
Before the introduction of this feature, if you want to create a relation between two tables so, for that either of these two tables must contain unique values. But if any of the tables does not contain any unique value then you need to create some extra work like the addition of new rows with unique values. After the introduction of this feature, you don’t require unique values to create relation between tables. In addition, we can now reduce the process for creating or adding new rows with unique values.
Limitation of the Many-to-Many relationship
Many-to-Many relationship feature comes with some limitations. Below are the Live Connect (multi-dimensional) sources that cannot be used with composite models:
- SAP HANA
- SAP Business Warehouse
- SQL Server Analysis Services
- Power BI datasets
- Azure Analysis Services
Conclusion
In this tutorial, I have discussed the new feature of Power BI desktop. This feature can help you to create a relationship between tables that does not contain unique values. This feature reduces the extra work that we need to do for creating relation between tables that does not have unique values. For more information about this feature, you can go through this link https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships. Thanks!
I excel when it comes to making bespoke data dashboards and visualizations that users and clients absolutely love. Sharing about things I enjoy doing is my hobby, whether it's about a project, collaboration, feedback, or just simple how-to guides about visualization.
If you have something to ask or share, I'd love to hear from you!
- Business Intelligence Vs Data Analytics: What’s the Difference? - December 10, 2020
- Effective Ways Data Analytics Helps Improve Business Growth - July 28, 2020
- How the Automotive Industry is Benefitting From Web Scraping - July 23, 2020