# using BLANK function

61 Reputation points
2020-09-28T10:33:27.793+00:00

Hi,
I have a question regarding the BLANK() function in DAX in Tabular.
I am asked to display BLANK wherever I get 0.
This is what I am doing:

Total count (60dg):=if([Rivers (60dg)]/[salePrice] = 0, BLANK(), [Rivers (60dg)]/[salePrice])

I want to know how correct is this? and will it do the calculation twice?
Also, there are some really big formulas. Is it a good idea to it this way? Is there any better way of doing it?
And the Total count (60dg) measure can be used in some other measure too for calculation. What will be the impact of showing BLANK() in such scenarios? Will it break the calculation?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,237 questions

1. 2,371 Reputation points
2020-09-28T23:11:37.377+00:00

I want to know how correct is this? and will it do the calculation twice?

It's not a great pattern, it's likely that it will perform the calc twice and if [salePrice] is 0 or blank it will throw an error.

Is there any better way of doing it?

DAX has a DIVIDE() function and it's normally better to use this as it will deal with divide by 0 errors automatically and you could capture the expression in a variable to avoid calculating twice (if you are using SSAS 2017 or later)

``````Total count (60dg):=
var _expression = DIVIDE( [Rivers (60dg)], [salePrice] )
return if( _expression = 0, BLANK(), _expression )
``````

Or, since logically the division can only be equal to 0 if the numerator is blank or zero you could just check that in your IF, then use the DIVIDE function to handle cases where [salePrice] is 0 or blank

``````Total count (60dg):=if([Rivers (60dg)] = 0, BLANK(), DIVIDE( [Rivers (60dg)], [salePrice]) )
``````

1. 5,816 Reputation points
2020-09-29T02:36:15.677+00:00

And the Total count (60dg) measure can be used in some other measure too for calculation. What will be the impact of showing BLANK() in such scenarios? Will it break the calculation?

This is also depends on your expressions. You need to consider for each measure, if the blank value will cause error in the query.