Forum Discussion

Naveen_K_Gattu's avatar
Naveen_K_Gattu
Regular Visitor
1 year ago
Solved

Need Help with blanks/Error in table visual

I am converting an Excel report to Power BI. When I convert same formulas into DAX , some results are not similar to Excel.

 

 

Excel returns #DIV/0! error but Power BI returns blanks, when I use result of Formula B column in Formula D, it considers blanks as 0 and returns 7 as result, but user expects me to show blank or error here.

 

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello Naveen_K_Gattu,

     

    Can you please try this approach:

    Formula A = DIVIDE(SELECTEDVALUE(Table[Column 2]), SELECTEDVALUE(Table[Column 1]))

    Formula B = DIVIDE(SELECTEDVALUE(Table[Column 4]), SELECTEDVALUE(Table[Column 3]))

    Formula D = IF(NOT(ISBLANK([Formula B])), ([Formula A] - [Formula B]) * SELECTEDVALUE(Table[Column 1]), BLANK())

4 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

    Hello Naveen_K_Gattu,

     

    Can you please try this approach:

    Formula A = DIVIDE(SELECTEDVALUE(Table[Column 2]), SELECTEDVALUE(Table[Column 1]))

    Formula B = DIVIDE(SELECTEDVALUE(Table[Column 4]), SELECTEDVALUE(Table[Column 3]))

    Formula D = IF(NOT(ISBLANK([Formula B])), ([Formula A] - [Formula B]) * SELECTEDVALUE(Table[Column 1]), BLANK())
  • sangeethray_92's avatar
    sangeethray_92
    Frequent Visitor
    1 year ago

    Can tou try this : 


    Formula B =
    IF(
    ISBLANK([Column 3]) || [Column 3] = 0,
    BLANK(),
    [Column D] / [Column 3]
    )

    Formula D =
    IF(
    ISBLANK([Formula B]),
    BLANK(),
    ([Column E] - [Formula B]) * [Column A]
    )

    If this post helps, then please give Kudos and consider Accept it as a solution to help the other members find it more quickly.

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from Sahir_Maharaj and sangeethray_92, please allow me to provide another insight.

    Hi Naveen_K_Gattu ,

     

    Please try the following DAX.

    Formula A = DIVIDE ( MAX ( 'Table'[Column2] ), MAX ( 'Table'[Column1] ) )
    Formula B = DIVIDE ( MAX ( 'Table'[Column4] ), MAX ( 'Table'[Column3] ), "ERROR" )
    Formula D = IFERROR ( ( [Formula A] - [Formula B] ) * MAX ( 'Table'[Column1] ), "ERROR" )

     

    The final result is as follows. Hopefully it will meet your needs.

     

    Please see the attached pbix for reference.

     

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Naveen_K_Gattu's avatar
    Naveen_K_Gattu
    Regular Visitor
    1 year ago

    I was using if (column = blank(),..) to solve it but it wasn't giving me the correct result, my measure had multiple conditions, so I used  VAR and few ISBLANK conditions and got the expected results.

    its strange that BLANK and ISBLANK dax returns different results.

    Thank you for the response!