556 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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|
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:
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:
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?
To create the calculated columns needed to show the species for each transaction (for example), start by creating a new calculated column like this:
Start the formula by typing =RELATED(.
Click on the target table, and click on the column within this that you want to show:
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:
Here I've renamed the column also.
You can now repeat this to bring in the quadrant and centre type names.
|Parts of this blog|
25 Aytoun Street