Visual Calculation_If blank, 0 and NaN

[AllanBerces]

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

[Jihwan_Kim · Accepted solution]

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.

[Praful_Potphode · Accepted solution]

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 NaN

after 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

[Jihwan_Kim · Accepted solution]

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.

[AllanBerces]

Hi @Jihwan_Kim @Praful_Potphode thank you very much working as i need

[Rufyda]

I'm glad you found a solution! We're here in the community to support each other. 🤝