DAX RELATED TABLE FUNCTIONS (2023)

DAX RELATED and RELATEDTABLEFunctions

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: RELATEDTABLE

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.