topic Re: Filter out NaN values in column in DAX Commands and Tips

Filter out NaN values in column

jinsbi — Thu, 10 Feb 2022 06:08:33 GMT

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

Re: Filter out NaN values in column

amitchandak — Thu, 10 Feb 2022 06:29:29 GMT

jinsbi ,

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

Re: Filter out NaN values in column

tamerj1 — Thu, 10 Feb 2022 07:07:47 GMT

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!

Re: Filter out NaN values in column

jinsbi — Thu, 10 Feb 2022 07:15:30 GMT

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