DAX: The RELATED() Function

Introduction

Today, a blog about the RELATED() function of DAX (Data Analysis Expressions) in PowerPivot. The RELATED function is a function that combines a field from another table into the table where you enter this this expression. In this blogpost I'll use an example of the Contoso retail example, that you can download from the Microsoft site.

Data model

Below is the datamodel shown, where there is a 1:n relation between DimProduct -> DimProductSubCategory -> ProductCategory. So one product can have one ProductSubCategory and multiple ProductSubCategories can belong to multiple Products.These relationships are called chained relationships because they form a chain that you can follow from the beginning
to the end in order to relate many tables. And in the multidimensional (and tabular) world we recognize this as a hierarchy construction.

In the screenshot above you can see fields ProductCategory and ProductSubCategory in the DimProduct table and these are not actually native fields but related columns from the DimProductSubCategory and ProductCategory.

Even if the original data model of Contose Sales has two distinct tables for category and
subcategory (which is correct for a standard database system), the data model is much easier
to query if we integrate certain fields in the DimProductSubcategory and ProductCategory in de DimProduct dimension and hide the DimProductCategory and ProductCategory.

RELATED() function

The RELATED() function relates columns from other tables within the table where you use the RELATED() function. Below you can see the expression that is used in the Dim Product table.

And this is the expression:

=RELATED(ProductCategory[ProductCategoryName])

You can only use the RELATED() function  when a relationship is defined.

Conclusion

This is a great function for combining data from multiple tables..

Greetz,

Hennie