BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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.

- Using the RELATED function to pull values from other tables
- 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:

You'll need to create the relationship shown too.

Add a measure to the animals table to count them:

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 problem is that there is no description for animals with no legs
(basically, snakes), because we didn't import one in the **Leggedness**
table:

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:

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:

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:

The headings in the pivot table will now be correct.

The pivot table now can read:

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:

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!

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