Need Help with blanks/Error in table visual

[Naveen_K_Gattu]

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.

[Naveen_K_Gattu]

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!

[v-denglli-msft]

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.

[sangeethray_92]

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.

[Sahir_Maharaj · Accepted solution]

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