Export (0) Print
Expand All

BLANK Function

Returns a blank.

Blanks are not equivalent to nulls. DAX uses blanks for both database nulls and for blank cells in Excel. For more information, see Data Types Supported in PowerPivot Workbooks.

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 details on the behavior of an individual function or operator, see DAX Function Reference.

The following example illustrates how you can work with blanks in formulas. The formula calculates the ratio of sales between the Resellers and the Internet channels. However, before attempting to calculate the ratio the denominator should be checked for zero values. If the denominator is zero then a blank value should be returned; otherwise, the ratio is calculated.

=IF( SUM(InternetSales_USD[SalesAmount_USD])= 0   , BLANK()   , SUM(ResellerSales_USD[SalesAmount_USD])/SUM(InternetSales_USD[SalesAmount_USD])   )

The table shows the expected results when this formula is used to create a PivotTable.

Reseller to Internet sales ratio

Column Labels

 

 

 

Row Labels

Accessories

Bikes

Clothing

Grand Total

2005

 

2.65

 

2.89

2006

 

3.33

 

4.03

2007

1.04

2.92

6.63

3.51

2008

0.41

1.53

2.00

1.71

Grand Total

0.83

2.51

5.45

2.94

Note that, in the original data source, the column evaluated by the BLANK function might have included text, empty strings, or nulls. If the original data source was a SQL Server database, nulls and empty strings are different kinds of data. However, for this operation an implicit type cast is performed and DAX treats them as the same.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft