using BLANK function

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

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
0 comments No comments
{count} votes

Accepted answer
  1. Darren Gosbell 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]) )
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Lukas Yu -MSFT 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.


    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.

    1 person found this answer helpful.
    0 comments No comments