Power BI RELATED Function
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 formula i.e. RELATED DAX Function. In this article, we will take you through one of the important RELATED DAX functions in Power BI.
What Does RELATED Function Do 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.
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.
Example of RELATED Function in Power BI
To demonstrate RELATED DAX function in Power BI, we have prepared below two data tables in excel 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.
- Above we have two tables “Product Table” and “Price Table”. In “Product Table” we have 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.
- 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”.
- Now give the name for the new column as Unit Price.
- Open RELATED function in power BI.
- We need to choose the column from the “Price_Table” but when you type the table name we don’t see any related searches.
This is because before we use RELATED function first we need to create a relationship between two tables under the “Data Modelling” tab.
- 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 common column is “Product”.
- To create a relationship click on the ellipsis (three dots) of any of the tables and choose “Manage Relationship”.
- This will open up below window for you, choose the “New” option.
- This will open the “Create Relationship” window.
- From the first drop-down list choose “Price_Table” and automatically in the below table it will choose “Product_Table”.
- Choose the common column between these two tables as “Product”.Now click on “Ok”
- Relationships will be created like then below one.
- Now go back and choose “New Column” again for “Product_Table” and 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.
- Close the bracket and hit enter key to get the price details in the new column.
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”.
- Instead of adding two extra columns we can arrive at the total value in the single-column itself, below is the formula to arrive the total price in single-step itself.
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.
Things to Remember Here
- The RELATED function works as VLOOKUP in Power BI.
- The RELATED function can be used only if there is any relationship between tables.
- Without a relationship, we cannot even get to see the table name and its column headings.
Recommended Articles
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 –
