using BLANK function

Question

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?
Please let me know.

Answer 1

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]) )

Answer 2

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.

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.