Secrets of Power BI RelatedTable using a simple example
This guide will help you understand two important DAX relationship functions:
- Related
- RelatedTable
If there is one function in Excel that every user, novice to experts, frequently uses, it is undoubtedly the VLOOKUP. Think of Related and Power BI RelatedTable functions as an efficient VLOOKUP that allows you to retrieve data or tuples from relates or connected tables in your data model. But what kind of relationship is required between the two tables for the use of these functions? What is the syntax? Can you use these functions to create a measure? You must have numerous questions in your mind, and this guide will help you answer all of them.
Relationship in Data Model
To utilize these functions, it is crucial that the two tables you want to use have a relationship. Related functions work for a table and its associated table if they have either a one-to-one or a one-to-many relationship. In summary, Related functions can be used for two tables that have:
- One-to-One Relationship
- One-to-Many relationship, a new column can only be defined from one side of the relationship
For Power BI RelatedTable function also require that two tables have a relationship:
- One-to-One Relationship
- Many-to-One Relationship, new column or measure can only be defined on the many side of the relationship
In the beginning, it can be not easy to grasps these functions, but the example below will help elaborate the concept in detail and enable you to become the DAX power user.
Related Function
The Related function essentially brings you the value from a connected or related table, just like a lookup. Here is how you can get the most out of this function. Related function overlooks any filters that you may have applies and evaluates all rows.
Syntax of related function
The syntax for this function is pretty simple:
RELATED(<column>)
The parameter for this function is the name of the column that you want to obtain your desired value from.
Example
This example will help you understand the function better. Suppose you have two tables:
Table A: Item Table that contains the name of the item along with its unit cost.
Table B: Stock Table that contains the item and its quantity in stock.
Tables A and B have a one-to-one relationship, and you want to calculate the total value of the stock in place. This is where Related function comes into play.
Create a new column and use the expression to make a new column with total stock value:
StockTotal = Stock [Quantity] * RELATED ('Item'[Cost])
Now that you understand how Related functions works, let us jump into Power BI RelatedTable function and see how it differs from Related.
Power BI Relatedtable Function
Unlike the Related function, RelatedTable function evaluates rows based on the applied filters. This is why it can be used as an alternative to CalculateTable function without the filters.
Syntax of relatedtable function
The syntax for this function is straightforward:
RELATED(<tableName>)
The parameter for this function is the name of the table that you want to obtain your desired value from.
Example
Let us consider the table A and B that we used for Related Function evaluation with a minor change in Table A. Table A has duplicates in the first column, assume that these two tables are from a different manufacturer.
Table A: Item Table that duplicated in first column
Now that two tables have many-to-one relationship, we can use RelatedTable function to calculate stock table value:
StockTotal = Stock [Quantity] * SUMX (RELATEDTABLE('Item'),'Item'[Cost])
In this formula, SUMX works as an iterator and retrieves the total stock value of all three items including any duplicate items.
Summary
Related and RelatedTable functions differ mainly in the side of the relationship they go from in a data model. But can be used to serve as a lookup in Power BI. The syntax of these DAX functions is simple and easy to understand.
You can watch complete YouTube video about Relatedtable function in power BI
For more useful blogs, please visit Learn DAX
