Power BI RELATED

In MS-Excel, we all have done the task of fetching data from one table to another, so in such cases, VLOOKUP is the household formula for all the excel users. Without using VLOOKUP at their workplace, most Excel users won’t end up there. The value of the VLOOKUP function in MS Excel has been stated, can we replicate the same formula in Power BI? We have a different formula, not exactly the VLOOKUP formulaVLOOKUP FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address are unique identifiers.read more, i.e., RELATED DAX Function. In this article, we will take you through one of the important RELATED DAX functions in Power BI.

RELATED as the name itself says, it will return the related value one table to another table. This is similar to the lookup value function we have in MS Excel, i.e., VLOOKUP.

However, in Power BI, before we apply this DAX function, we need to create a data model relationship between tables we are referring to.

Below is the syntax of the RELATED DAX function in Power BI.

Power BI RELATED Syntax

We just need to select the result column from the other table, and it will fetch the details for us. If you are finding it difficult to understand with this theoretical explanation, don’t worry because, in the below, we will give you practical examples in detail.

Power-BI-RELATED

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI RELATED (wallstreetmojo.com)

To demonstrate the RELATED DAX function in Power BI, we have prepared below two data tables in excelData Tables In ExcelA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.read more worksheet.

Below are examples of the RELATED function in Power BI. You can download the workbook to use the same file as we used in this example.

You can download this Power BI RELATED Function Excel Template here – Power BI RELATED Function Excel Template
Power BI RELATED Example Data
  • Above we have two tables “Product Table” and “Price Table.” In “Product Table,” we have the product name and units sold details with the “Sales Rep” name for each product.
  • In the “Price Table,” we have product name and their price per unit values, so we will use the RELATED function to fetch the price details to “Product Table.”Upload the above two table data to the Power BI Desktop file.
 Tables
  • Now from “Price_Table” we need to fetch the cost price of each product to the “Product_Table.”Right-click on the “Product_Table” and choose the option of “New Column.”
related dax function - New Column
  • Now give the name for the new column as Unit Price.
Unit Price New Column
  • Open RELATED function in power BI.
Function
  • We need to choose the column from the “Price_Table”, but when you type the table name, we don’t see any related searches.
related dax function - Column Price

This is because before we use the RELATED function first, we need to create a relationship between two tables under the “Data Modelling” tab.

Power BI RELATED - Data Modelling
  • As you can see above, we don’t have any relationship between these two tables. The relationship can be created between these two tables by using the common column between these two tables, so in these two tables, the common column is “Product.”
Note: Power BI is intelligent enough to create an automatic relationship between two tables based on the column headings when we upload the data tables. Because as a new learner, you need to know about the relationship between two tables, we have removed the relationship.
  • To create a relationship, click on the ellipsis (three dots) of any of the tables and choose “Manage Relationship.”
Manage Relationships
  • This will open up the below window for you. Choose the “New” option.
Power BI RELATED - New option
  • This will open the “Create Relationship” window.
related dax function - Create Relationship Window
  • From the first drop-down list, choose “Price_Table,” and automatically in the below table, it will choose “Product_Table.”
PriceTable with ProductTable
  • Choose the common column between these two tables as “Product.”Now click on “Ok.”
common column
  • Relationships will be created like then below one.
Power BI RELATED - Relationship
  • Now go back and choose “New Column” again for “Product_Table” and open the RELATED function.
Open RELATED function
  • As you can see above, we have a table name with all the columns of the related table. Choose the “Price_Table [Price]” column from the list.
related dax function - Price Table Price Column
  • Close the bracket and hit the enter key to get the price details in the new column.
 price details

There you go; we have a VLOOKUP kind of formula to fetch the details from one table to another based on the common column between tables.

  • Since we have fetched price details, we can arrive in new columns as “Total Value” by multiplying “Units Sold with Units Cost.”
related dax function - Total Value
  • Instead of adding two extra columns, we can arrive at the total value in the single-column itself. Below is the formula to arrive at the total price in the single-step itself.
 related dax function - Calculate Column

Like this, by using the RELATED function in Power BI, we can fetch the data from one table to the other.

Note: Power BI RELATED function file can also be downloaded from the link below, and the final output can be viewed.

You can download this Power BI RELATED Function Template here – Power BI RELATED Function Template

Things to Remember Here

This has been a guide to Power BI RELATED. Here we discuss how to use the RELATED function in power bi to return the related value of one table to another table with the help of an example. You can learn more about Power BI from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>