Power BI Data Model with Multiple Relations on the same Table (2023)

By: Harris Amjad |Updated: 2023-05-19 |Comments | Related: > Power BI

Problem

Sometimes one needs multiple relations between thesame tables in a Microsoft Power BI dashboard. However, in modeling, you will see that Power BI makesone of the relationships inactive during a calculation or visualization.This tutorial highlights the DAX function, whichassists in making inactive relations active as per the need of the calculation orvisualization. This tutorial will act as a step-by-step guide to using DAX to assistin creating multiple relations between the same table in Power BI desktop forbusiness intelligence projects.

Solution

With the adventof the big data revolution, our world has become highly saturated with large volumesof information, which continues to evolve our data needs and related technologies.To ensure that different entities efficiently leverage this data, we use a computerizedsystem of storing and extracting data through a database management system (DBMS).As an overview, databases consist of different tables in which each row can be uniquelyidentified by a specific column (field) known as the primary key. However, a relationaldatabase's real power and structure lies in establishing relationships betweenits tables. By connecting related data across tables, one can create complex andmeaningful queries that allow you to extract valuable insights for data analysis. Thistutorial will assess how to create multiple relationships between the same tablesin Power BI.

For example, in the above picture, we can see the Power BI data model, allowing us to observe how the data isorganized and related to each other within a database. Each block represents a tablealongside its relevant fields/columns. More importantly, the lines and arrows betweenthe tables signify a cardinality relationship within the schema. It is also interesting to notethat these lines are marked with a '1' and '*'.What does this mean?

These symbols refer to the different typesof relationships in a database. They can be:

1. One-to-One:This relationship exists when a single record in one table is related to onlyone record in another table and vice versa. For example, let's say youorganize information about your employees and want to keep track of their passports.This is a classic example of a one-to-one relationship, as each person can onlyhave a single passport, and each passport is issued to only one person. Suchrelationships, however, tend to be uncommon as we can easily store correspondinginformation in the same table. In the example above, a line connected by two'1'son either side will signify this type of relationship.
2. One-to-Many:This relationship exists when a single record in one table can be related tomultiple records in another table. However, eachrecord in the second table can only be relatedto one record in the first table. An example of this relationship would be customersand their orders. A single customer can place multiple orders; however, eachorder only belongs to one customer. This is the most common type of databaserelationship, and in the schema above, it is represented by an '*'and a '1' between the relevant tables.
3. Many-to-Many:This relationship exists when multiple records in one table can be related tomultiple records in another table. For instance, in a publishing company'sdatabase, the authors and books may have a many-to-many relationship as eachbook can be written by several authors, and each author can also write severalbooks. A junction table is utilized in normal use cases to prevent data duplication.It typically includes two foreign keys that reference the primary keys of theoriginal tables, along with any additional fields specific to the relationshipbetween the two tables. This relationship is signified by two '*'on either side of the line in the schema above.

Considering the wide diversity of DBMS use cases,it is possible that multiple relationships of the types we have discussed abovemay originate from our tables. We will now outline a practical demonstration wherebywe will create a schema in MySQL and then observe and manipulate our data modelin Power BI.

Creating a Schema in MySQL

For this demonstration, we are concerned with themonthly sales of a bookstore. Currently, our schema will be comprised of a singletable with the following fields:

• Order date
• Shipping date
• Product key
• Customer ID
• Total sales made

To get started, we will first create our databaseand then access it using the following commands:

CREATE DATABASE bookstore_sales;USE bookstore_sales;

Now, we will create our table using the followingstatements, which will include the fields mentioned above:

CREATE TABLE monthly_sales( order_date date, ship_date date, product_key varchar(6), customer_id varchar(8), sales int unsigned);

Now that we have a skeletal structure for our table,we can populate it by executing the following commands:

INSERT INTO monthly_sales VALUES("2023-02-01", "2023-02-02", "BK1234", "LHR78902", 20),("2023-02-02", "2023-02-02", "BK4568", "LHR34901", 50),("2023-02-04", "2023-02-04", "MV1890", "KHI17392", 30),("2023-02-04", "2023-02-05", "BK0098", "LHR19028", 10),("2023-02-05", "2023-02-05", "MV2900", "RWP16370", 60),("2023-02-06", "2023-02-07", "MG1902", "MUL29104", 30),("2023-02-09", "2023-02-15", "MG1839", "SIA38291", 40),("2023-02-10", "2023-02-15", "BK2891", "LHR00382", 80),("2023-02-14", "2023-02-15", "BK0032", "ISL10394", 30),("2023-02-15", "2023-02-15", "MV9001", "LHR01919", 20),("2023-02-18", "2023-02-20", "MV8834", "MUL18301", 50),("2023-02-20", "2023-02-20", "MG0219", "KHI32413", 40),("2023-02-22", "2023-02-27", "BK6002", "ISL90029", 80),("2023-02-23", "2023-02-27", "BK9921", "BWP82716", 60),("2023-02-26", "2023-02-27", "MG0192", "LHR10284", 40);

Lastly, we can inspect our table using the SELECT statement in MySQL:

SELECT * FROM bookstore_sales.monthly_sales;

This will output our source table, as seen below.

Multiple Relationships in Power BI

Now that we have a database, we can import it intoPower BI to observe multiple relationships emerging between the same tables.

Step 1

Before we can delve into the essence of our topic, we need to import our databasefrom MySQL to Power BI. To do that, click Get data in theHome ribbon and click More… at the bottomof the resulting list. As shown below, we can observe a range of common data sourceswe can work with in Power BI.

Step 2

The Get Data window will appear. Below the search box, select theDatabase category and choose the MySQL database option towards the right, as shown below. Thenclick Connect at the bottom of the window. This step is flexibledepending on the platform your database is hosted on.

Step 3

The MySQL databasewindow will appear. Enter the relevant server and databasecredentials in the dialog boxes and click OK.

Step 4

The Navigator window will pop up if Power BI successfully connects with yourdatabase. Below Display Options, check the selection box besidethe "bookstore_sales.monthly_sales" table and click Loadat the bottom of the window. Power BI also enables users to observe the tables theyare loading, as shown below. If there are any anomalies with the dataset, we canalso cater to that using the Transform Data option at the bottomof the window, which will take us to a Power Query Editor window, where tools areavailable to manipulate our dataset. However, we don't need to delve intothat as our database is complete and clean.

Step 5

After successfullyloading our selected tables to Power BI, we are ready to move toward the essenceof our main problem. Currently, our database only consists of a single table. Asit is a single entity, there is no possibility for database relationships.

For our purposes, we will focus on book sales byshipping and orders. Before we can compute these numbers, we must create a datetable that encodes all information regarding the dates required in this dataset.It will include a date column encompassing year,quarter,month, andday alongsideseparate columns for "month", "year", "monthindex", and a "yearmonth" field thatcaptures concatenated year and month.

In the main interfaceof Power BI, under the Modelingribbon, click New table, asshown below.

In the formula box, enter the following DAX formulaand click Enter:

DateTable = GENERATE ( CALENDAR ( DATE ( 2023, 2, 1 ), DATE ( 2023, 2, 28 ) ), VAR currentDay = [Date] VAR startYear = 2023 VAR month = MONTH ( currentDay ) VAR year = YEAR ( currentDay ) RETURN ROW ( "month", month, "year", year, "month index", INT ( ( year - startYear ) * 12 + month ), "YearMonth", year * 100 + month ) )

Step 6

Select the Table icon in the Visualizations panelto observe our newly created table, as shown below. This is a pre-built visual,and we can populate it by selecting or dragging all the columns of the "DateTable"to the "Columns" field, as shownbelow.

We can now see our table in the main working spaceof Power BI, presented below.

Step 7

Now that we have two tables in our database, there is a possibility of relationshipsbetween them. Click the Model view icon towards the right (seebelow) to visualize our current database schema.

To establish relationships between the same tables,drag the "Date" column from our "DateTable"and connect it to the "order_date"column of the other table. This will createour first relationship. For our second one, again drag the "Date"column and connect it to the "ship_date"column of our "monthly_sales" table.This process is outlined below:

We can observe that the "DateTable"and "monthly_sales" tables havetwo one-to-many relationships. However, it is interesting to note that the relationshipbetween the "Date" and "ship_date"is rendered inactive (shown by the dashed line) as PowerBI only allows one active connection between the tables.

Step 8

We now need to figureout a method to utilize the inactive relationship. To see what our data is doing,we can go back to our report view panel, where on another page, we will be creatinga new table visual. After selecting the Tableicon, we can once again populate our "columns"field by selecting the "Date" and "Sales"columns as outlined below.

Our resulting table is as follows:

We can see how the data is categorized and how thesales add up to 640 units. What if we want to distinguish the sales by order dateand shipping date?

Step 9

We need to compute two separate measures that sumup the sales by order dates and shipping dates. To do so, in the Home ribbon, click on New measure,as shown below:

To create our first measure that explicitly sumssales by order dates, enter the following DAX formula in the formula bar:

OrderDateSales = CALCULATE ( SUM ('bookstore_sales monthly_sales'[Sales] ), USERELATIONSHIP ( 'DateTable'[Date], 'bookstore_sales monthly_sales'[order_date] ) )

To create the other measure for salesby shipping dates, repeat the above process with the following DAX formula:

ShipDateSales = CALCULATE ( SUM ('bookstore_sales monthly_sales'[Sales] ), USERELATIONSHIP ( 'DateTable'[Date], 'bookstore_sales monthly_sales'[ship_date] ) )

Step 10

Now that we have our explicitly calculated measures,create another table visual and populate it with the "sales"and "Date" columns like before,but for the new measures as shown below.

We can now see (below) that our book sales havebeen categorized according to the order dates and shipping dates, and all of themadd up to our initial total of 640 units. This offers clarity that our calculationis working properly.

Step 11

We can also visualizethe two relationships using different graphical visuals like a line chart. Selectthe Line charticon in the Visualizationspanel and populate the "X-axis"field with the "Date" column, asshown below. For the "Y-axis," selectthe "OrderDateSales" and "ShipDateSales"measures.

After a few customizations,below is our new visual:

We can now clearly observe that for mostof February, the bookstore shipped a larger volumeof sales than it took orders. In other words, the store consistently took ordersthroughout the month but preferred to ship in bulk, reducing shipping costs andincreasing efficiency, thus optimizing their supply chain strategy.

Conclusion

In this tip, we have discussed some of the fundamentalsof databases, including the different types of database relationships. We then extendedthis concept to the possibility of multiple relationships between the same tables.We practically demonstrated that by building a schema in MySQL and manipulatingit in Microsoft's Power BI.

Next Steps

• Check out all thePower BI Tips on MSSQLTips.com

About the author

Article Last Updated: 2023-05-19