Using the RELATED function to link tables, and how to test for blanks in DAX
Part one of a two-part series of blogs

You can make pivot tables much easier to use by combining all of the aggregator columns into a single table, using the RELATED function. This blog also shows you how to work with blanks, including using the ISBLANK function to test whether matching values exist in linked tables.

  1. Using the RELATED function to pull values from other tables (this blog)
  2. Using the BLANK Function in SSAS Tabular Models

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 05 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using the RELATED function to pull values from other tables

The RELATED function allows you to combine fields from different tables, to make pivot table field lists tidier and easier to use:

Without RELATED With RELATED
Without RELATED With RELATED

When you can use RELATED

Let's start with when you can pull fields in from other tables.  Two conditions have to be satisfied.  The first is that the tables are directly or indirectly linked to each other:

Linked tables

We can show the species name field in the transactions table because there is a link between them (you can hop relationships from transaction to product to animal to species).

The second condition is that you should be at the child end of any relationships:

One-to-many relationships

Each species has lots of animals, each animal has lots of products and each product has lots of transactions.

A good way to think about this is: if I use RELATED to show a field in a particular table, will SSAS Tabular have a unique value to show in every case?

Creating the RELATED function

To create the calculated columns needed to show the species for each transaction (for example), start by creating a new calculated column like this:

RELATED function

Start the formula by typing =RELATED(.

Click on the target table, and click on the column within this that you want to show:

Species table and column

Click on the Species table, then on the SpeciesName column within this.

This will automatically create the correct syntax.  Type ) and then press Enter to create your first calculated column:

Renamed column

Here I've renamed the column also.

 

You can now repeat this to bring in the quadrant and centre type names.

  1. Using the RELATED function to pull values from other tables (this blog)
  2. Using the BLANK Function in SSAS Tabular Models
This blog has 0 threads Add post