Forum Discussion

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
10 months ago
Solved

Visual 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

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    10 months ago

    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's avatar
    Praful_Potphode
    10 months ago

    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

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    10 months ago

    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's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    10 months ago

    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