Forum Discussion
AllanBerces
Post Prodigy
Post ProdigyVisual Calculation_If blank, 0 and NaN
Hi can anyone help me on my visual calcualtion result, if the result is blank will be equal to 0 if the result is NaN will be equal to 0 %Actual_rev = [Earned]/[Total Hrs] Thank you
Hi,
I am not sure how your semantic model looks like, but please try something like below.
Please check the below picture and the attached pbix file.
Hi AllanBerces
ideal way of handling NaN will be in DiIVIDE DAX function or PowerQuery.
add below powerquery step to convert NaN to null
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [#"%Actual_rev"]<> null and Number.IsNaN([#"%Actual_rev"]) then null else [#"%Actual_rev"]) // replace [#"%Actual_rev"] with column which has NaNafter handling the NaN, you can try below Visual Calculation.
[%Actual_rev] = format(SWITCH(TRUE(), ISBLANK( [Earned]/[Total Hrs]),0 , [Earned]/[Total Hrs] ),"0")Please give Kudos or mark it as solution once confirmed.
Thanks and regards,
praful
Praful_Potphode
Super User
Super UserHi AllanBerces
ideal way of handling NaN will be in DiIVIDE DAX function or PowerQuery.
add below powerquery step to convert NaN to null
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [#"%Actual_rev"]<> null and Number.IsNaN([#"%Actual_rev"]) then null else [#"%Actual_rev"])
// replace [#"%Actual_rev"] with column which has NaNafter handling the NaN, you can try below Visual Calculation.
[%Actual_rev] = format(SWITCH(TRUE(),
ISBLANK( [Earned]/[Total Hrs]),0
, [Earned]/[Total Hrs]
),"0")Please give Kudos or mark it as solution once confirmed.
Thanks and regards,
praful