RELATED

How to Use the Power BI DAX function RELATED

In this article, we will focus on the DAX function RELATED, which is used to retrieve data from related tables in a data model. We will discuss what the function does, how to use it, and provide some examples to illustrate its usage.

Understanding the RELATED Function

The RELATED function in DAX is used to retrieve data from a related table in a data model. It is often used in conjunction with the bi-directional relationship feature in Power BI, which allows tables to be related to each other in both directions.

When you use the RELATED function, Power BI looks for a related table based on the current context of the formula. It then retrieves the value from the related table based on the matching key value.

For example, suppose you have two tables: Sales and Products. The Sales table has a foreign key that relates to the Products table. You can use the RELATED function to retrieve data from the Products table based on the foreign key value in the Sales table.

Using the RELATED Function

To use the RELATED function in Power BI, you need to follow these steps:

1. Identify the table that contains the foreign key that relates to the related table.

2. Create a bi-directional relationship between the two tables.

3. Write a DAX formula that uses the RELATED function to retrieve data from the related table.

Let’s take a closer look at each step.

Step 1: Identify the Tables

Before you can use the RELATED function, you need to identify the tables in your data model that have a relationship between them. In our example, we have two tables: Sales and Products.

The Sales table contains a foreign key called ProductID that relates to the Products table.

Step 2: Create a Bi-Directional Relationship

To use the RELATED function, you need to create a bi-directional relationship between the two tables. This allows Power BI to identify the related table when you use the RELATED function.

To create a bi-directional relationship in Power BI, follow these steps:

1. Go to the Relationships view in Power BI.

2. Select the Sales table and click on Manage Relationships.

3. Click on New to create a new relationship.

4. Select the Products table as the related table.

5. Select the ProductID column in the Sales table as the foreign key.

6. Select the ProductID column in the Products table as the primary key.

7. Check the bi-directional option.

Step 3: Write a DAX Formula

Once you have created the bi-directional relationship, you can use the RELATED function to retrieve data from the related table.

To use the RELATED function, you need to write a DAX formula that includes the function and the column you want to retrieve data from. For example, suppose you want to retrieve the Product Name from the Products table based on the ProductID value in the Sales table. You can write the following formula:

= RELATED(Products[ProductName])

This formula retrieves the ProductName column from the Products table based on the matching ProductID value in the Sales table.

Examples of Using the RELATED Function

To illustrate the usage of the RELATED function, let's consider some examples.

Example 1: Retrieving Data from a Related Table

Suppose you have two tables: Sales and Products. The Sales table contains a foreign key called ProductID that relates to the Products table. You want to create a table that shows the total sales amount by product name.

To achieve this, you can follow these steps:

1. Create a new table.

2. Add the ProductID column from the Sales table to the new table.

3. Add the SalesAmount column from the Sales table to the new table.

4. Write a DAX formula that uses the RELATED function to retrieve the Product Name from the Products table.

The DAX formula for the new table would be:

Sales by Product =

SUMMARIZE(

Sales,

Products[ProductName],

“Total Sales Amount”, SUM(Sales[SalesAmount])

)

This formula retrieves the ProductName column from the Products table using the RELATED function and calculates the total sales amount for each product.

Example 2: Filtering Data from a Related Table

Suppose you have two tables: Sales and Products. The Sales table contains a foreign key called ProductID that relates to the Products table. You want to create a chart that shows the total sales amount for a specific product category.

To achieve this, you can follow these steps:

1. Create a slicer that contains the Category column from the Products table.

2. Write a DAX formula that uses the FILTER function to filter the Sales table based on the selected category.

3. Write a DAX formula that calculates the total sales amount.

The DAX formula for the chart would be:

Total Sales by Category =

CALCULATE(

SUM(Sales[SalesAmount]),

FILTER(

Sales,

RELATED(Products[Category]) = SELECTEDVALUE(Products[Category])

)

)

This formula filters the Sales table based on the selected category using the RELATED function and calculates the total sales amount for the filtered data.

The RELATED function in DAX is a powerful tool that allows users to retrieve data from related tables in a data model. By using the bi-directional relationship feature in Power BI, users can easily create formulas that retrieve data from related tables.

In this article, we discussed what the RELATED function does, how to use it, and provided some examples to illustrate its usage. By following the steps outlined in this article, you can start using the RELATED function in your Power BI reports and dashboards to retrieve data from related tables.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)