topic Re: Need Help with blanks/Error in table visual in DAX Commands and Tips

Need Help with blanks/Error in table visual

Naveen_K_Gattu — Wed, 05 Feb 2025 09:35:24 GMT

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.

Re: Need Help with blanks/Error in table visual

Sahir_Maharaj — Wed, 05 Feb 2025 10:38:33 GMT

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

Re: Need Help with blanks/Error in table visual

sangeethray_92 — Wed, 05 Feb 2025 18:21:01 GMT

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.

Re: Need Help with blanks/Error in table visual

Anonymous — Thu, 06 Feb 2025 02:15:43 GMT

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.

Re: Need Help with blanks/Error in table visual

Naveen_K_Gattu — Thu, 06 Feb 2025 07:50:39 GMT

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!