**DAX RELATED and RELATEDTABLE**Functions

In this blog, we’ll be looking at DAX RELATED and RELATEDTABLE functions.

If you prefer to watch the YouTube video, rather than reading the blog, please see the video below:

## RELATED DAX FUNCTION

First, wewill be looking at the RELATED function, whichrequiresa column name as an inputandreturns a related value from another table.

RELATED(<column>)

This sounds very familiar, it sounds a lot like an Excel Vlookup, but only better. It works on models with a one-to-one relationship and models with a Many-to-One relationship. However, important to understand is that the calculation needs to lookup values from the one side drawing figures to the Many-Side. We’ll go into those details a bit later as this does require some creative DAX writing.

To start off with let’s have a look at our model, wehave two tables,a‘Products’table and a ‘StockLevels’ table and between these tables, we have a one-to-one relationship.

Ifwe look at the‘Products’ table we seeseveralcolumn valuessuch as‘ITEMCODEs’,‘ITEMNAMES,product sales, % and theunit price.

If we look at the‘StockLevels’table,it’s got the same amount of‘ITEMCODEs’and‘ITEMNAMES’and it gives us the available quantity of stock, ‘QTY’.

Let’s see how this function works with an example.

*Our Example Question: RELATED*

*Let’s say that we would like to calculate the value of our current inventory on hand. **Our ‘Products’ table contains the unit price, but does not include the stock levels, the stock level value is in our ‘Stock Levels’ table. Thus we will use the RELATED function to match the corresponding values in the two tables in order to calculate the value of our current inventory on-hand.*

Todetermineourcurrent inventory on hand we would need to multiply the stock value with the amount ofstockon hand. Our stock value is in our ‘Products’ table and our quantity on hand in our ‘Stock Levels’ table.

To write our first related function we will create a calculated column in the ‘Products’ table, called ‘Stock Value (related)’. We will multiply the ‘Unit Price’ column from the ‘Products’ table with the column ‘QTY’ in the related table, ‘Stock Levels’. In other words, I want to use the product’s unit price and multiply it by the stock levels and quantity for each item.

Stock Value (relates) = Products [Unit Price] * RELATED (‘Stock Levels’ [QTY])

We called the calculated column from the ‘Products’ table, so what RELATED does it search for the matching ‘ITEMCODE’ of the ‘Products’ table in the ‘Stock Levels’ table and return the QTY thereof. It then iterates over each ‘ITEMCODE’ and returns the result of ‘QTY’ times the ‘Unit Price’.

We can also use a measure to determine our stock value, we will call it ‘StockV(related). We will use SUMX for the ‘Products’table and the expression is the ‘UnitPrice’ from the ‘Products’ table multiplied by‘QTY’ column from theRELATEDvalue in the‘StockLevels’table.

DAX:

StockV(related) =SUMX(Products

,Products[Unit Price] *RELATED(‘Stock Levels’ [QTY]))

This is avery similar function to what we just did,but in thissituation,Iused theSUMXfunction for the measure so that it will iterate over each row in our table.

Solet’s create our first visual hereto see the result of our 2 functions.

As you can see it was calculated perfectly in both cases, fantastic we’ll come back to related in a moment.

**DAX RELATEDTABLE**

Next, in this post about DAX RELATED and RELATEDTABLE Functions. Let’s have a look at the RELATEDTABLE function, which requires a table name as an input and returns a related value from another table.

RELATEDTABLE(<tableName>)

This will return a table with all the rows related to the current table. RELATEDTABLE works on models with a One-to-One relationship and on models with a Many-to-One relationship. It can also lookup values from the Many-Side and draw figures into the One-Side.

*Our Example Question: RELATED**TABLE*

*Let’s say that we would like to calculate the value of our current inventory on hand. **Our ‘Products’ table contains the unit price, but does not include the stock levels, the stock level value is in our ‘Stock Levels’ table. Thus we will use the RELATEDTABLE function to match the corresponding values in the two tables in order to calculate the value of our current inventory on-hand.*

From the ‘Products’ table, we will create a calculated column named ‘StockValue(relatedtable)’.First,use the SUMX function which requires a table and an expression as input. We use the RELATEDTABLE function to call our related table,in this case, the‘StockLevels’table for the table input of SUMX. The expression that we will use to calculate our current inventory on-hand is the ‘UnitPrice’from the ‘Products’ tablemultiplied by the stock levelQTY from the ‘Stock Levels’ table.

DAX:

Stock Value (relatedtable) =SUMX(RELATEDTABLE(‘Stock Levels’)

Products[Unit Price] * ‘Stock Levels’ [QTY])

Great, we can see that RELATEDTABLEdid a perfect job as it gave us the exact same numbers as our RELATED function.

Let’s do the exact same as above, but just as a measure for the RELATEDTABLE functionandcall it‘StockV(relatedtable).

For the measure, we are going to use the SUMX function oftherelated table ‘Stock Levels’and use another SUMX for the expression of the first SUMX function. We use the ‘Products’table as input, where we will multiply the Products‘UnitPrice’by the‘QTY’ of our ‘StockLevels’ table.

DAX:

StockV(relatedtable) =SUMX(RELATEDTABLE(‘Stock Levels’)

,SUMX(Products,

Products[Unit Price] * ‘Stock Levels’ [QTY])

So, you may be asking what exactly this formula do? It iterates over the related table which is the ‘Stock Levels’ table and then it does another iteration of the ‘Products’ table where it multiplies the products ‘Unit Price’ by the ‘QTY’ in the ‘Stock Levels’ table.

Let’s see the result of theStockV(related) and theStockV(relatedtable)calculations. We can see that we haveexactly the samecalculation or answer for each ‘ITEMNAME’.

Until now all our examples have been with a One-to-One relationship between the ‘Products’ table and the ‘Stock Levels’ table.Meaning each ITEMNAME appears only once in each table.

Let’s have a closer look at a Many-to-One relationship, we will change our model and add a duplicate productin our ‘Products’table. Let’s take the product with the ITEMCODE D1607B and create aduplicaterow. We will make a few changes to this duplicate product so that it’s information is not exactly the same.

When we close and apply wesee an error message.

It says the column ‘ITEMCODE’ in table products contains a duplicate value D1607 B and this is not allowed for columns on the One-Side of a Many-to-One relationship. In order to solve this, we need to change from a One-to-One relationship to a Many-to-One relationship.

Take a closer look at how our relationship has changed.

Something to note is that all our calculations (our measures and calculated columns) are still calculating correctly without any errors. Until now all our formulashave used the related table as the‘StockLevels’ tables. Evenin our calculated columns, the related table is the ‘Stock Levels’ table.

Note now that the‘Stock Levels’tableis the One-Sideof our One-to-Manyrelationshipsand our‘Product’ tableis the Many-Side.What we want to test now is what will happen if we change our measures and calculatedcolumnsso that the ‘Products’ table is the related table. This means that we will initiate our calculations from the ‘other side’, from the ‘Stock Levels’ table.

### DAX RELATED, ONE-TO-MANY

From the ‘Stock Levels’ table, we create a new calculated column, called ‘StockV(related) levels. We again use the SUMX function and the ‘Stock Levels’ table as the table andforthe expression, we’ll multiply the ‘Stock Levels’ table‘QTY” with theRELATEDtable ‘Products’.What you will note here is that the equation does not recognise the ‘Products’ table, see the red in the DAX formula below.

DAX:

StockV(related) =SUMX(‘Stock Levels’

,‘Stock Levels’ [QTY] *RELATED(products[…..)

Why does this happen?Whenyoulook upvalues in a Many-to-One relationship for the RELATED function the calculation needs to read values only from the One-Side. Here we are trying to read values from the Many-Side to the One-Side,this is unfortunately not possible withRELATEDand we’ll see why in a moment.

**RELATEDTABLE, ONE-TO-MANY**

Let’s do the same thing but let’s use theRELATEDTABLEfunction.

From the ‘Stock Levels’ table, we create a new calculated column, called ‘StockV(relatedtable) levels. For the measure, we use the SUMX function and as the RELATEDTABLE ‘Products’table as our input table and multiply the‘QTY’ from the‘Stock Levels’ table with the‘Unit Price’ from the ‘Products’ table.

DAX:

StockV(relatedtable)levels=SUMX(RELATEDTABLE(Products)

,‘Stock Levels’ [QTY]*Products[Unit Price])

By using the REALTEDTABLE function it recognises both the‘StockLevels’andthe ‘Products’columns.

Let’s put this into a table and evaluate the output.

You can see it works when we look up the value from the Many-Side to the One-Side when using the RELATEDTABLE function. This is exactly where the difference lies between DAX RELATED and RELATEDTABLEFunctions. RELATED can only draw values from the One-Side to the Many-side. So, it looks up the values in the One-Side and it populates the Many-Side.

For RELATEDTABLE it can look up values from the Many-Side and bringing them into the One-Side. That is the clear difference between the RELATED and RELATEDTABLE functions. I hope this has put some clarification on this topic, it may seem a little bit confusing if you are new to this but the best way again to be familiar with how this works is to practice, play around with it and see how it reacts when you swap the related tables around in both functions.

For more great posts go to our blog page. To learn more about Power BI, join our training.

## FAQs

### DAX RELATED TABLE FUNCTIONS? ›

The RELATEDTABLE function starts from the table on the one-side of the relationship and gives access to the table on the many-side of the relationship. RELATEDTABLE is a table function, and returns a table of values that contains all of the rows on the many-side that are related to the current row on the one-side.

**What is the difference between related and related table in DAX? ›**

RELATED is one of the most commonly used DAX functions. You use RELATED when you are scanning a table, and within that row context you want to access rows in related tables. **RELATEDTABLE is the companion of RELATED, and it is used to traverse relationships in the opposite direction**.

**What is the difference between related and related table functions? ›**

**RELATED can only draw values from the One-Side to the Many-side**. So, it looks up the values in the One-Side and it populates the Many-Side. For RELATEDTABLE it can look up values from the Many-Side and bringing them into the One-Side. That is the clear difference between the RELATED and RELATEDTABLE functions.

**How do I reference another table in DAX? ›**

In DAX, you can **use the RELATED() function if the current two tables have already a relationship**. Or, you can use the DAX LOOKUPVALUE() function if you can't create a relationship between the two tables. Also, you could try to use merge or append in the Power Query Editor.

**How do you relate tables in DAX without using relationships? ›**

The basic pattern is **DAX CALCULATE with TREATAS + a possible CROSSFILTER to remove an existing table relationship in the calculation evalution**. With TREATAS first is mentioned the column which is being filtered and second the column which is automatically filtered like the first mentioned column.

**What is the use of related table? ›**

The RELATEDTABLE function **performs a context transition from row context(s) to a filter context, and evaluates the expression in the resulting filter context**. This function is a shortcut for CALCULATETABLE function with no additional filters, accepting only a table reference and not a table expression.

**How do you create a relationship between two tables in DAX? ›**

- On the Modeling tab, select Manage relationships > New.
- In the Create relationship dialog box, in the first table drop-down list, select a table. Select the column you want to use in the relationship.
- In the second table drop-down list, select the other table you want in the relationship.