Using the RELATED function to link tables, and how to test for blanks in DAX
Part two 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
  2. Using the BLANK Function in SSAS Tabular Models (this blog)

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 BLANK Function in SSAS Tabular Models

Blanks are similar to nulls in SQL Server or Access, but have a number of important differences.  This blog page shows these differences, and shows how to use the ISBLANK function.

Our worked example

If you want to follow along with this example, download this Excel workbook and import the two worksheets into a new model to get:

Final diagram

You'll need to create the relationship shown too.

Add a measure to the animals table to count them:

Counting animals

Create a measure to count the number of animals.

 

You can now create the following pivot table:

The pivot table fields The pivot table itself
The pivot table fields The pivot table itself

The problem is that there is no description for animals with no legs (basically, snakes), because we didn't import one in the Leggedness table:

Zero legs missing

There is no description for zero-legged animals (possibly because I can't imagine what it would be!).

 

A solution - combine RELATED and BLANK

What we can do is to ask the following question: if you try to look up the Description field in the Leggedness table, does this return a blank because there isn't a description to return?

To put this into practice, start with creating a calculated column to look up the Description field in the Animal table:

Leggedness looked up

This column looks up for each animal's number of legs what the corresponding description would be. Snakes - as ever - are the problem.

You can now wrap this formula in an ISBLANK function:

Test for blank returned

Here we're testing to see whether the value returned for a given number of legs is blank (ie we weren't able to find a matching record).

 

You could then complete this formula to get:

=IF(

ISBLANK(RELATED(Leggedness[Description])),

"No legs",

RELATED(Leggedness[Description])

)

This gives the following:

Correct categories

The headings in the pivot table will now be correct.

The pivot table now can read:

The final pivot table

The leg description looks much better.

 

Testing for BLANK()

Instead of using the ISBLANK function, it might have been neater to derive the final result in two stages:

Two stage solution

This solution creates a column called Interim, then a column called Final.

 

Here are the formula for the two columns:

Column Formula
Interim =RELATED(Leggedness[Description])
Final =IF([Interim]=BLANK(),"No legs",[Interim]) 

You can use ISBLANK and =BLANK() interchangeably.

Blank arithmetic

Blanks do not work for arithmetic in the same way as nulls in Access or SQL Server:

Operation Example How BLANK() is treated Result
Addition, subtraction BLANK() + 5 As zero 5
Multiplication, division BLANK() * 3 Cascades through BLANK()
Combinations BLANK() && False Ignored False

Combining conditions

Note the symbols to show whether either or both of two conditions are true:

Symbol What it means
&& Two things are both true
|| Either or both of two things is true

 

And that's the end of this blog!

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