Power Pivot Principles: The A to Z of DAX Functions – BLANK

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week our functions draw a BLANK…

The BLANK() function takes no arguments and returns a blank.

That’s right.  The above is truly a blank expression (groan – Ed.).

It should be noted that:

• blanks are not equivalent to nulls
• DAX uses blanks for both database null values and for blank cells in Excel
• some DAX functions treat blank cells somewhat differently from Microsoft Excel.  Blanks and empty strings ("") are not always equivalent, but some operations may treat them as such.  For example, if the original data source were a SQL Server database, null values and empty strings are different kinds of data.  However, when BLANK() is called, an implicit type cast is performed and DAX treats them as the same.

As an example, imagine you wanted to build a ‘Profit Margin’ measure:

Nothing seems to be wrong with this formula, even Power Pivot thinks so.  But what if there were no sales in one period?  We would get a division by zero error.  To avoid the potential error, we can use the IF function to provide us with an error trap:

This will return a zero value when the sales are nil. But why report it anyway? We can use the BLANK function to further streamline this measure viz.

Now the value will be suppressed in the Pivot Table if sales are blank, leading to clearer, more concise reports.

(It is acknowledged the DIVIDE function could have been used here, but we are trying to demonstrate an application of the BLANK function.)

Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.