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, we will be looking at the RELATED function, which requires a column name as an input and returns 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, we have two tables, a ‘Products’ table and a ‘Stock Levels’ table and between these tables, we have a one-to-one relationship.
If we look at the ‘Products’ table we see several column values such as ‘ITEMCODEs’, ‘ITEMNAMES, product sales, % and the unit price.
If we look at the ‘Stock Levels’ 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.
To determine our current inventory on hand we would need to multiply the stock value with the amount of stock on 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 ‘Unit Price’ from the ‘Products’ table multiplied by ‘QTY’ column from the RELATED value in the ‘Stock Levels’ table.
DAX:
StockV (related) = SUMX(Products
,Products[Unit Price] * RELATED(‘Stock Levels’ [QTY]))
This is a very similar function to what we just did, but in this situation, I used the SUMX function for the measure so that it will iterate over each row in our table.
So let’s create our first visual here to 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 ‘Stock Value (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 ‘Stock Levels’ table for the table input of SUMX. The expression that we will use to calculate our current inventory on-hand is the ‘Unit Price’ from the ‘Products’ table multiplied by the stock level QTY from the ‘Stock Levels’ table.
DAX:
Stock Value (relatedtable) = SUMX(RELATEDTABLE(‘Stock Levels’)
Products[Unit Price] * ‘Stock Levels’ [QTY])
Great, we can see that RELATEDTABLE did 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 function and call it ‘StockV (relatedtable).
For the measure, we are going to use the SUMX function of the related 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 ‘Unit Price’ by the ‘QTY’ of our ‘Stock Levels’ 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 the StockV (related) and the StockV (relatedtable) calculations. We can see that we have exactly the same calculation 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 product in our ‘Products’ table. Let’s take the product with the ITEMCODE D1607B and create a duplicate row. 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 we see 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 formulas have used the related table as the ‘Stock Levels’ tables. Even in our calculated columns, the related table is the ‘Stock Levels’ table.
Note now that the ‘Stock Levels’ table is the One-Side of our One-to-Many relationships and our ‘Product’ table is the Many-Side. What we want to test now is what will happen if we change our measures and calculated columns so 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 and for the expression, we’ll multiply the ‘Stock Levels’ table ‘QTY” with the RELATED table ‘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? When you look up values 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 with RELATED and we’ll see why in a moment.
RELATEDTABLE, ONE-TO-MANY
Let’s do the same thing but let’s use the RELATEDTABLE function.
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 ‘Stock Levels’ and the ‘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 RELATEDTABLE 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. 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.
Leave A Comment