Home » RELATED Function DAX

RELATED Function DAX

RELATED function is a Power BI Relationship function in DAX which returns a related value from another table.




The RELATED function requires that a relationship exists between the current table and the table with related information.

If a relationship does not exist, you must create a relationship.

RELATED function requires the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table.

SYNTAX
RELATED(<column>)

column is the column that contains the values you want to retrieve.

Lets look at an example demonstrating a RELATED function in Power BI.

As you can see, Here we have a two tables named as Item and SalesDetails.

There is a relationship(one to many) exists between these table, and the column name is ItemId.

Lets Look at the data in these tables. As you can see the SalesDetails table contains the ItemId, SalesPerson, and total sold quantity details while Item  table contains the ItemId, Item Name and their Price.

 

Lets use the RELATED function to fetch the price details to SalesDetails table for each Item.

For this we will create calculated column in table SalesDetails as shown below.

Right click on table SalesDetails then click on new column.

 

Now Create the following DAX expression named Price that use RELATED function.

You will notice that as soon you write Related Function, it gives you suggestion that which column you want from the related table, as you can see it suggests all the columns from Item table.

As you want to fetch the Price so select Price from suggested Item table, and commit the DAX.

Price = RELATED('Item'[Price])

 

 

Once you commit the Dax, a calculated column is created in SalesDetails table as shown below.

Lets quickly verify to ensure that it fetchs the price from Item table in table SalesDetails.

Go to Data view, and you can see now Price column in table SalesDetails contains the Price against each Item.

Using RELATED Function in Measure

Lets say you want to display the total sales done by SalesPerson that would be a Total SalesAmount = Total Quantity * Total Price

Lets create a measure named TotalSalesAmount in SalesDetails table.

TotalSalesAmount =

SUMX ( SalesDetails, RELATED ( 'Item'[Price] ) * SalesDetails[Quantity] )
Reason of using SUMX function is that SUMX function evaluates expression for each row in table is that and RELATED function needs row context so every single row in SalesDetails table is lookup by Realted funtion.

After commit the DAX, Lets drag the measure TotalSalesAmount into table visual.

You can see, it returns the total sales amount done by Sales Person.

The RELATED function requires that a relationship exists between the current table and the table with related information.

Lets see, if there is no relationship between table Item and SalesDetails as shown below.

 

Now when you try to use Related function then you will get following error.

 




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.