Creating & Using Linked Tables in Power BI

One benefit of PowerBI is once you include two tables on the dashboard, they will be connected.

If you click on a table, the table linked is able to filter the selected value. (It makes use of connections between tables to accomplish this as we’ve explained previously.)

Let’s examine how this system works.

(This post is part the Power BI Guide. Utilize the menu at the top to navigate. )

How do you create linked tables?

For illustration, here is an example of a report (dashboard) we’d like to create:

  • On the left side, we will see transaction categories from our financial accounts.
  • On the right side, the details of the transaction.

The information is taken from data in the transactions.csv files. (You are able to download your statement from the banks if wish to follow along.)

The left side of the data is categorical data. The information on the right is transaction data.

To translate this into terms of SQL the data that is left is the basic data:Copy

Select the category, then add(*) from transactions

The information on the right side is:Copy

Select * from the list of transactions

We make use of the relationship wizard within PowerBI in order to connect them onto the same elements categorical. After that, when we place 2 tables in the dashboard Power BI uses this relationship to allow us to access the tables according to categories. This means that we can view all of the office expenses and advertising costs, travel costs, etc.

This is how the report looks like once we release the report on powerbi.com:

This is how the details of the transaction appear like:

Groups by categories

These are the categories. To get this data view we have to include data from the source of the data transactions.csv again and then remove every column other than category. Then , we select group by category.

Then, select the visualization of the table and the fields. For the category table , we will select one field, a one category.

In the table of transactions, we choose all the fields that are related to transactions. Under fields , we have two sources of data:

  • Categories
  • Transactions

The dashboard can be resized to fit your needs.

This is how the tables appear when placed on the dashboard. The table and the table text aren’t large enough and are not put in the proper position when we begin. Therefore, grab the edges and change them and navigate on to Actual Size/Page View so that they are larger enough for reading.

Relationships to be seen

Here’s this screen for the relationship screen. There is no need to do anything since Power BI matches by an element that is common to all, the category.

When we design the table prior to publishing it on powerbi.com we are able to test the table. It is not possible to see the layout clearly, which is the entire screen or mobile layout.

We can also click on the category left. The table to the right is updated to show only transactions within the category we have selected. It is also known as the synchronized table.

Full-size view

Like always, you can select Publish to Power BI to try your final copy. And , as we’ve already stated, it’s the only way to view the full size screen since Power BI Desktop doesn’t offer a great preview feature.

Leave a Reply

Your email address will not be published. Required fields are marked *