Forum Discussion

jinsbi's avatar
jinsbi
New Member
4 years ago
Solved

Filter out NaN values in column

Hi All,

I have a column called "Value" in my PBI file,  which is of Decimal Number type by default also containing "NaN".  "NaN" came directly from the source file.  So it causes my DAX function to display NaN. Is there any way to avoid it? ( Without replacing NaN to 0). 

my DAX is 

Energy export = CALCULATE(sum(Turbine_Data[Value]),FILTER(Turbine_Data,Turbine_Data[Signal]="Energy Export (kWh)"))

 

 

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi jinsbi 

    I think you need to remove NaN at row level to avoid erros in your calculation. Try:

    Energy export =
CALCULATE (
    SUMX ( Turbine_Data, IF ( Turbine_Data[Value] > 0, Turbine_Data[Value] ) ),
    Turbine_Data[Signal] = "Energy Export (kWh)"
)

    Please let me know if solved your problem. If so, kindly mark my reply as accepted solution. Thank you!

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    jinsbi ,

    Energy export = iferror( CALCULATE(sum(Turbine_Data[Value]),FILTER(Turbine_Data,Turbine_Data[Signal]="Energy Export (kWh)")),0)

    • jinsbi's avatar
      jinsbi
      New Member
      4 years ago

      Thanks for the response.It helps to replace NaN with 0. But it makes total calculated as 0 instead of 3400653.00

       

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi jinsbi 

    I think you need to remove NaN at row level to avoid erros in your calculation. Try:

    Energy export =
CALCULATE (
    SUMX ( Turbine_Data, IF ( Turbine_Data[Value] > 0, Turbine_Data[Value] ) ),
    Turbine_Data[Signal] = "Energy Export (kWh)"
)

    Please let me know if solved your problem. If so, kindly mark my reply as accepted solution. Thank you!