What is Power BI RELATED Function?
The Power BI RELATED function is a Relationship function under DAX (Data Analysis Expressions) function used to retrieve a single value from a related table based on a defined relationship. It allows you to access data from a related table without creating complex joins or lookups in your query.
Table of contents
- The RELATED function in Power BI allows you to retrieve values from related tables based on defined relationships.
- It simplifies querying by eliminating the need for complex joins or lookups.
- The Power BI RELATED function evaluates the entire table while performing a lookup and it doesn’t consider any filter conditions during this evaluation.
- Ensure that relationships are properly established and the data types of the related columns match.
- The RELATED function is used in calculated columns and measures, and it operates based on the current row context where based on the current row value scan is performed on other tables for the instance of that row value.
- Be mindful of potential issues with circular references in your data model.
- Similar to the RELATED function, you can also use other relationship functions such as RELATEDTABLE, CROSSFILTER or USERRELATIONSHIP to manage and define the relationships between the tables.
How To Use RELATED Function Do in Power BI?
To use the RELATED function in Power BI, follow these steps:
- Ensure that there is a relationship established between the tables involved in the query.
- Use the RELATED function in a calculated column or measure and specify the column from the related table to retrieve data.
The syntax of a Power BI RELATED function is:
RELATED(<ColumnName>) – Column name you want to retrieve.
- Power BI will automatically follow the defined relationship and retrieve the corresponding value from the related table based on the context of the calculation.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
- RELATED function provides quick query execution and faster data processing capabilities compared to other functions such as LOOKUPVALUE.
- You can use the RELATED function in the FILTER function as a nested DAX formulas.
- The RELATED Filter allows you to establish a relationship between the tables and then filter the table for further calculation.
- You can use Power BI RELATED DAX expressions to define a relationship and then use it in conjunction with aggregate or filter functions such as CALCULATE, SUM, AVERAGE, FILTER, etc. Power BI RELATED DAX can’t perform any calculations on a Standalone basis.
- However, Power BI RELATED DAX can be a powerful way to perform calculations based on the relationships between the tables.
- Power BI RELATED DAX expressions can be used to define Power BI RELATED Column.
- A RELATED Column is used to establish the relationship between the tables, and then, using this relationship, you can extract values from the Related table.
- The Power BI RELATED Column can help you analyze and access insights across multiple tables without defining any complex calculations.
In the subsequent section, we will go through some of these examples to understand how you can use the RELATED function in Power BI.
Examples of RELATED Function in Power BI
In this section, we will see two examples where we will provide a step-by-step process to use the RELATED function in Power BI.
In this example, we will apply the RELATED function using the Country_Product_Sales dataset in Power BI. The Country_Product_Sales dataset contains insights into the financials of product sales across different countries, along with applicable Tax rates. To use the RELATED function in Power BI, follow these steps:
Step 1: Open the Power BI Desktop and import the Country_Product_Sales dataset using Data Connection in POWER BI. You can also customize the dataset required by using the Transform Data option.
Once imported into Power BI, you will be able to view the data fields in the Fields pane.
Step 2: Navigate to the Model View pane. You will see that Power BI has already automatically mapped the relationships across the tables.
You can also additionally define a relationship in the Model View pane.
Here Power BI has defined the relationship between Tax Rate and Country-wise Product Sales using the Country data field by specifying the source and Target table, cardinality, and cross-filter direction sections.
Step 3: Navigate to the Fields pane, right-click on the Country wise Product Sales, and select the New column from the menu bar.
Step 4: Enter the DAX expression in the formula bar and Click on the Commit icon to save the changes.
It will create a new Country Tax Rate % column for the Country-wise Product Sales dataset.
Step 5: Format the Country Tax Rate % column to display the Tax Rates in percentages.
Step 6: Navigate to the Data view pane. You can see the newly created column Country Tax Rate % values populated against each country.
Step 7: Navigate to the Visualization pane, choose Table Visual, and add the data fields from the Fields pane to the Columns section. You can see the Country Tax Rate values in the Power BI Table.
In this example, we will use the RELATED function in Power BI using the US_Superstore_Dataset file. The US_Superstore_Dataset file contains various order details of a US Superstore across the US states and the Salesperson across the regions in the US. To use the RELATED function, follow these steps:
Step 1: Open the Power BI Desktop and import the US_Superstore_Dataset file using Data Connection.
Once imported to Power BI successfully, you can see the data attributes in the Fields pane.
Step 2: Navigate to the Model View pane to establish the relationship between the tables.
Power BI automatically establishes a relationship between the tables based on the dataset. However, you can also customize the relationship by specifying the details, such as cardinality, cross-filter direction, and the attributes to be mapped.
Step 3: Now Navigate to the Fields pane, right-click on the Orders table, and choose the New column option.
Step 4: Enter the DAX expression in the formula bar and click on the Commit icon to save the changes.
It will create a new column Salesperson Name in the Orders table in the Fields pane.
Step 5: Navigate to the Data View pane. You can see the values of the Salesperson Name populated based on the Region data field.
You can drag and drop the data fields from the Fields pane to report canvas and choose the Table visual to see the Power BI Table.
Note: Sometimes, the RELATED function may not work as expected. In those scenarios, ensure you have created a relationship between the tables.
Instead of using a Power BI RELATED function, you can also use other related functions such as the Power BI Related USERRELATIONSHIP. Power BI Related USERRELATIONSHIP function enables you to specify the relationship to be used in a specific calculation that exists between two columns. One of the key features of the USERRELATIONSHIP function is that it allows you to override the default, active relationship between the tables till the duration of the calculation.
Important Things to Note
- The RELATED function works based on the current row context. It retrieves the related value corresponding to the current row being evaluated.
- You can only use this function in calculated column expression or as a nested function in an expression using a table scanning function.
- The function relies on a good relationship between tables. Ensure that the relationship between the current table and the table with related information is properly defined, and the columns used for the relationship match data types.
- The Power BI RELATED function examines all the values in the related table irrespective of any filters applied to it.
- Be cautious with circular references, where multiple tables have relationships that lead back to the same table. It can cause unexpected results or errors when using the RELATED function.
- You can’t use the Power BI RELATED function to retrieve a column across a limited relationship, i.e., one-side column values.
Frequently Asked Questions (FAQs)
In Power BI, the RELATED and RELATEDTABLE functions are used to establish relationships between tables and perform calculations based on those relationships. However, they have different purposes:
The difference between LOOKUPVALUE and the Power BI RELATED function is highlighted below:
LOOKUPVALUE Function: LOOKUPVALUE is a DAX function that retrieves a single value from a column in a table based on specified search criteria. It does not rely on table relationships like RELATED does. Instead, it allows you to search for a value in a column and return a corresponding value from another column in the same row. in the same row.
Power BI RELATED Function: The RELATED function retrieves a value from a related table based on a relationship between tables. It can only be used in calculated column expressions or as a nested function in an expression using a table-scanning function, as it needs a row context to perform a lookup.
If the POWER BI RELATED function is not working as expected, there could be several reasons for it:
• Missing or incorrect relationships: Ensure that the tables involved have the appropriate relationships established, and that the columns used for the relationship are of the same data type.
• Circular reference: Check if there is a circular reference in your data model, which can cause issues with the RELATED function.
• Incorrect syntax: Review the syntax of your DAX formula to ensure it is written correctly.
• Filtering issues: Verify that there are no conflicting filters or slicers applied that may affect the results of the RELATED function.
• Aggregation context: Understand the context in which you are using the RELATED function, as it may require proper aggregation or evaluation within the correct context.
By examining these aspects, you should be able to troubleshoot and resolve any issues with the Power BI RELATED function.
This has been a guide to Power BI RELATED. Here we learn to use Related DAX function to retrieve a single value from related table with examples & downloadable template. You can learn more from the following articles –