DAX RELATEDTABLE Function

DAX (Data Analysis Expressions) relationship functions in Power BI allow users to manage and navigate relationships between tables. These functions are essential for creating complex data models and ensuring accurate calculations across related tables.

Syntax:

RELATEDTABLE(<table>)

Description:

Returns a table that contains rows related to the current row. This function is used to create a table of related rows based on the existing relationships in the data model.

Example:

Consider the following two tables:

Sales Table

Products Table

            

                                 

We want to create a new calculated column in the Products table called "TotalSales" that sums the sales amounts for each product.

Steps to Create the Calculated Column in Power BI Desktop

1. Ensure Relationship: Make sure there is a relationship between the Sales table and the Products table on the ProductID column. You can check this in the "Manage Relationships" dialog.

2. Open Power BI Desktop.

3. Load your data into the data model.

4. Go to the Data view by clicking on the table icon on the left pane.

5. Select the Products table.

6. Click on "New Column" in the Modeling tab.

7. Enter the DAX formula into the formula bar:

8. TotalSales = SUMX(RELATEDTABLE(Sales), Sales[SalesAmount])

9. Press Enter to create the calculated column.

Result

The Products table will now have an additional column called "TotalSales":

In this example, the RELATEDTABLE function is used to get the rows from the Sales table that are related to each ProductID in the Products table. The SUMX function then calculates the total sales amount for each product by summing the SalesAmount column for these related rows. This enriches the Products table with a new column that shows the total sales amount for each product.