The Related function in Power BI is an essential function that we must learn to use in Power BI in order to take advantage of the resources and possibilities when doing calculations and operations on tables, and which, as we will see, helps us to use when make calculated fields, but also when we want to make new measurements. The Related function in Power BI allows you to perform actions in Power BI with the values of columns or fields that are in different tables as long as there is a relationship between the two tables.
With the Power BI Related function we can perform these operations:
- Get values from another table
- Allows us to perform operations on data from other tables directly when using calculated fields
- The Related function, as we will see below, is very useful to be able to perform operations with data from other tables when we create a measure.
Condition to be able to use Related in Power BI
In order to use Related in any of the 3 cases, we are going to need the two tables with which we are going to work to be related to each other, as we have mentioned at the beginning, it is the first factor that must be taken into account for Related to work .
Remember that the relationship between tables is a key factor to be able to work in Power BI when we work with more than one table. Not only to be able to use Related, in general to be able to work correctly with a data ecosystem with connected tables.
Using a common field, as we have already explained in how to make a data model in Power BI, we will be able to establish a relationship between the two tables with which we are going to work..
- The table where we are going to add the data
- The table where the data comes from
Let's see with an example how to use Related to get a column from another table. We will also see how to use Related to be able to make a field calculated by performing an operation between fields that are in different tables. And finally we will see how to use Related in a measure to be able to do calculations with fields that are in different tables.
In the example we are going to see data from a company that sells computer products that are separated into two tables:
- PRODUCT SALE TABLE: In one of the tables we have the information about the products sold and the No. of sales of each product
- PRODUCT PRICE TABLE: In the other table, we have the price of each product.
- Both tables have a column with the product ID (IDU Column), which we have used to be able to relate the two tables in the data models, and therefore be able to use the fields of all the tables to make calculations.
1 Use Related to bring a column from another table in Power BI
In order to bring us a column from another table in Power BI we would use the Related function as we have learned on other occasions. It is a simple way to add information to a table with information from another table.
Remember that, for the Related function to work, there must be a relationship between the table where we are performing the operation, and the table where the field or column you want to retrieve is located.
In order to bring data from one table to another in Power BI, once there is a relationship between the tables, as we have said, we have to create a new column in the table where we want to add the data.
In this case we have to go to the "Data" view and in the taskbar look for "New Column", an option that we will find both in the start menu and in the table tools menu..
When creating the new column, we are going to change the name. In this case we are going to call the column "PRODUCT PRICE", in order to better identify what information this table will contain.
Once we have created the new column, and changed the name of the column, after the equal in the Power BI formula bar we are going to start writing "Related" which, as we will see, will give us the suggestion of the function directly, on which we can click..
By selecting the suggestion of the Related function, we will see the fields of the other table we are working with: Product Price. We search and select with a click the column that we want to take to the PRODUCT SALES table. In this example case, the field that we want to bring up is the "UNIT PRICE". Clicking this field is already added to the formula, and we would only have to press the enter key on the keyboard. We will see how we already have the new column created with the data. We will see in summary how using Related in Power BI we have brought the price column from the other table.
Important
If at some point after writing Related in the Power BI operations bar, we see that the suggestion of fields to choose from does not appear, we would write after the parenthesis the name of the field that we want to bring back and we will see how it will show us the suggestion of the field on which we should click.
2 How to use Related in a formula to make a Calculated Field
Also, as we have seen, we can use Related in an operation or calculation to be able to put fields that belong to different tables in the same formula. It is a simple way of being able to squeeze the benefits of a calculated field. In order to perform an operation with Related using fields directly from different tables, as we have said, there must be a relationship in the data model between the tables where the columns that we are going to use in the formulas are located.
Following the example that we were seeing, if we have a data table where the sales information of the products that a company has sold is found, and in another table we have the column of the unit price per product, we are going to directly calculate the income that has been sold. generated sales of these products. We will use a calculated field to be able to do this operation, in which we will have to multiply the sales column of the "PRODUCT SALE" table by the unit price column of "PRODUCT PRICE".
Important
Remember how we have explained that we will be able to carry out this operation with a measurement. We will later use the measure to carry out this same operation following the example.
Steps to use Related in a calculated field
In order to make a calculated field, we already know that the first thing we have to do is create a new column. We will find this option in the Power BI Desktop taskbar.
Now next we are going to change as we already know the name of the new column created, directly in the Power BI formula bar. In this case, since we are going to calculate the income with a calculated field, we are going to call this column “Calculated Field Income”.
Now in the formula or operations bar, after the equal, we are going to start writing the name of the column where the sales are. In the example, we are inside the view module, in the "PRODUCT SALE" table.
We start writing "SALES" and the Power BI suggestion of the sales column will appear.
We click on it to place the sales field in the formula. Remember that, in the formula of a calculated field, when putting the name of a field, it is represented between square brackets [ ]; And that the name of the table is placed in front between commas``. That is why we use Power BI suggestions, which help us in this sense to interpret or translate the formulas into the DAX language.
Next, we add the multiplication sign (*) and then write Related. We will see that Power BI also shows us the functions and expressions as a suggestion. Just as we do when we want to include a field in the formula, we will also use the formula hints to apply them in the operation bar.
We click on Related, and it will show us field suggestions from the other table. Since we want to add the unit price column to the formula, we select the “UNIT PRICE” field. Then we press the enter key on our keyboard.
Now we have the complete formula, which would look like this, and we will have the calculated column, the "Calculated Field Income" column where we can see the result of the operation by row.
In this way we have been able to use Related in a formula to be able to make a calculated field with fields found in different tables.
As we already explained in "what is a measure in Power BI", measures are a very useful Power BI resource that we can use to make calculations in our data model.
The good news is also that Related can also be used when making a Measure in Power BI.
Let's see in the next section how we can use Related in a measure in Power BI.
3 How to use Related in a Power BI Measure
We are going to continue with the same example we are working with, so we can compare the result obtained with the calculated field and the measure in Power BI. Remember that, to make a measure, we do not have to create a new column.
Steps to use Related in a Measure in Power BI
To calculate a measure in Power BI with Related, as we already explained in "how to make a measure in Power BI" within the data view, we are going to go to "Start" or "Table tools" on the taskbar. There we find the "New measure" option, as we see in the image below.
Once we click on "New measure", we are going to change the name of the measure, as we always recommend doing, in order to put a name that helps identify what type of measure it is. In this case, continuing with the example, we want to calculate revenue using a new measure. Therefore, we will call the new measure: “Revenues Calculated with Measure”.
Now after the equals we are going to start writing the formula, putting first the DAX expression that we want to apply. Since in this case we want to do a multiplication that takes the row context into account, we are going to use the SUMX function. We start by typing SUMX in the operation bar and we will select the function when we see it in the suggestion window.
Next, we make a line break, and we begin to write the name of the table where we are going to carry out the measurement, clicking on the suggestion when we see it.
Next, we will see that the name of the table has already been added. We add a comma with in the image and with the Shift Enter key we go down the line again.
Now we are going to carry out the operation on this line. We would start by writing the name of the columns that we want to multiply (“Sales columns in the example”), followed by the multiplication sign (*) and then write Related. We will see that again we get the suggestion of the Related function that we have to click on.
When clicking on the suggestion, we will see how a suggestion window is displayed, where the names of the fields of the other table that we have connected appear. So, we would click on the “UNIT PRICE” hint, the name of the other column that we want to use to multiply it by sales.
Now we only have to close the formula with two parentheses. We then validate the measurement by clicking on the check that we find to the left of the operations bar.
Now we have the measure created with the Related function that we have used to multiply two fields that are in different tables.
If we want to check that the measurement is correct and it gives us the same result as the column calculated that we have done before, we can add a new column to the table, we would put a name and after the equal we begin to write the name that we have given to the measure created. We will see that now it also gives us the created measures as a suggestion. We click on the suggestion and as we see in the image, the calculated field and the measure give us the same result.
As you have been able to verify, the Related function is going to be a resource that you will use very frequently in Power BI Desktop, as we have said to be able to take advantage of all the fields or columns of all the tables of the model with the condition that the tables must be connected.
It is very common to think at the beginning, when we are using Power BI for the first few times, that in order to be able to perform operations between fields that are in different tables, you have to join tables and build a table where you have all the fields. This concept goes back a long way with the Related function, which allows you to think big, with all the fields in your data model, without having to build a single table. The fact of being able to work in this way will save us a lot of time since now it is not necessary to cross-reference information. We have already seen a tremendously easy task to relate the tables in the model, which is also performed by Power BI by default when we upload several tables that have a field in common. Even having to undo the relationship that Power BI establishes automatically, and do it by hand,
All these functions, tools and resources of Power BI are what will allow us to work with large volumes of data with great agility. For this reason, it is important to learn these utilities that will give us special abilities to be able to handle a lot of data easily.
Think big, act simple and orderly. Do not forget, as we have said on other occasions, that we must pay attention to all phases of a project in Power BI; And that, ensuring the veracity of the data, we will be taking giant steps in carrying out a professional project. Power BI has these tools that are easy to use, but you have to know them. The interface is intuitive, and the resources it offers us make our task easier. We are going to encounter some difficulties at the beginning, especially when creating measures, if we are not used to using the DAX language. However, we are going to show you how easily we can carry out any measure in our project, even without knowing the existence of that measure. Today there are other applications that are outside the range of Power BI that can help us in the most complicated parts and that TechnoWikis will explain to you to turn complicated tasks into simple tasks. Always as we have said, paying attention to the fact that the data we are working with is good, well organized, there are no errors, and we work with a standardization of nomenclatures.
