7 Replies Latest reply on Jul 25, 2017 8:56 PM by Prayson Wilfred Daniel

# Replce null based on condition

Hi Guru's

I have a scenario like below

When city=bangalore I want to replace in 0 in place of NULL for particular city.

Can any one help me out on this scenario.

Regards,

Kumar

• ###### 1. Re: Replce null based on condition

Hi Kumar,

Try this to replace null to 0.

zn(lookup(sum([Measure]), 0))

Above will work when null in place.

Want to replace only city=bangalore else replace all null to 0.

Thanks

sankar

• ###### 2. Re: Replce null based on condition

IIF([City]='Bangalore',ZN(SUM([Value])),SUM([Value]) )

Reads: If City is Bangalore, then Zero if value is NULL, else value.

• ###### 3. Re: Replce null based on condition

Hi Sankarmagesh,

Thanks for your quick reply. If we use that calculation it will make zero for measure wherever it is having null value.

But I want like below

If [city]='Banaglore and [measrefield]=null then I want to replace for that city measure value as 0.

Regards,

Kumar

• ###### 4. Re: Replce null based on condition

Hi Prayson.

We can't mix aggreagate and non aggregate values. Can you see below screen shot

Regards,

Kumar

• ###### 5. Re: Replce null based on condition

Ah! Wrap city with ATTR

IIF(ATTR([City])='Bangalore',ZN(SUM([Value])),SUM([Value]) )

or disagregated form by removing SUM

IIF([City]='Bangalore',ZN([Value]),[Value] )

1 of 1 people found this helpful
• ###### 6. Re: Replce null based on condition

Thanks Prayson. It is working now.

• ###### 7. Re: Replce null based on condition

You are welcome